I’ve been banging my head against my computer for hours over this, and yet it doesn’t seem to help.
Table GenSurgNCOP is a listing of surgical procedures, each record being one procedure. It has a date field, dtPat_In_Or, for the date of the procedure. And it has a local key field, LCN.
Table GenSurgComorbid is a table of postop office visits. The foreign key is LCN, and there’s a field FollowupDate.
What I want to do is return one row (or none, as a null) in GenSurgComorbid for each row in GenSurgNCOP, such that FollowupDate is the latest date not exceeding DATEADD(yy, 1, dtPat_In_Or), or something like that.
In other words, if dtPat_In_Or is ‘7/18/05’, and three records in Comorbid (with the same LCN) have the dates ‘10/30/05’, ‘1/27/06’, and ‘7/19/06’, I want it to return ‘1/27/06’.
Yes, I do something similar almost every day. You need a subquery.
Here is a similar example from something I did:
WHERE
d.benefit_eff_date = (select max(d1.benefit_eff_date)
from dependent_association d1
WHERE d.control_id = d1.control_id
and d.ssn = d1.ssn
and d.dep_ssn = d1.dep_ssn)
One way would be an old method of ripping through the file and writing a work file where you update the record if the date is more recent and fits your criteria or adds the record if it is not yet in the work file. Now you have and easy to use work file.
select FollowupDate, * from GenSurgNCOP n
left join GenSurgComorbid c
on n.LCN = c.LCN
WHERE --n.LCN = ‘0008CC’
c.FollowupDate =
(SELECT MAX(c1.FollowupDate)
FROM GenSurgComorbid c1
WHERE c.LCN = c1.LCN
and c1.FollowupDate < dateadd(yy, 1, n.dtPat_In_Or))
Brilliant! I’ve done stuff like that before, but for some reason I experienced brain slippage.
