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’.
Can you think of any way to do this?