# Help with a SQL query?

**URL:** <https://boards.straightdope.com/t/help-with-a-sql-query/412437>\
**Category:** Factual Questions\
**Created:** [July 19, 2007, 7:39pm UTC](https://boards.straightdope.com/t/help-with-a-sql-query/412437 "2007-07-19T19:39:35Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![tdn](https://avatars.discourse-cdn.com/v4/letter/t/94ad74/32.png) [@tdn](https://boards.straightdope.com/u/tdn)\
**Post date:** [July 19, 2007, 7:39pm UTC](https://boards.straightdope.com/t/help-with-a-sql-query/412437/1 "2007-07-19T19:39:35Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Shagnasty](https://avatars.discourse-cdn.com/v4/letter/s/9dc877/32.png) [@Shagnasty](https://boards.straightdope.com/u/Shagnasty)\
**Post date:** [July 19, 2007, 7:50pm UTC](https://boards.straightdope.com/t/help-with-a-sql-query/412437/2 "2007-07-19T19:50:49Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![What\_Exit](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/what_exit/32/10652_2.png) [@What\_Exit](https://boards.straightdope.com/u/What_Exit)\
**Post date:** [July 19, 2007, 7:50pm UTC](https://boards.straightdope.com/t/help-with-a-sql-query/412437/3 "2007-07-19T19:50:53Z")

</div>

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.

Jim (RPG programmer, some SQL skills)

---

<div class="post-metadata">

**Author:** ![tdn](https://avatars.discourse-cdn.com/v4/letter/t/94ad74/32.png) [@tdn](https://boards.straightdope.com/u/tdn)\
**Post date:** [July 19, 2007, 8:22pm UTC](https://boards.straightdope.com/t/help-with-a-sql-query/412437/4 "2007-07-19T20:22:52Z")

</div>

**Shagnasty** ’s idea wins.

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.

Thank you! 🙂