Ask SQL LJ

[woodwardiocom]

I'm reasonably ept in SQL, but this one is just a skoosh beyond me. Take the following SQL:

select pat.[LM File Ref], pat.[Atty File Ref],
pat.[Application Serial #], pat.[Title of Application],
pat.[Date of Filing], pat.[Status Of Application],
pat.[Related Applications],

due.actiondue AS 'Next Critical Action',
due.duedate AS 'Next Critical Due Date'

from L0562_Report pat
left outer join tblpatactiondue act
on pat.appid = act.appid
left outer join tblpatduedate due
on act.actid = due.actid

where due.datetaken is null
order by pat.[Atty File Ref], due.duedate

For any row from pat, this can return multiple rows from due. I only want the one row from due with the earliest duedate. I can do this with a GROUP BY and HAVING, yah?

Comments

[shaix.livejournal.com]

Try restating you from clause like this:

from L0562_Report pat
left outer join tblpatactiondue act
on pat.appid = act.appid
left outer join (select actid, min(duedate) duedate where datetaken is null) oldestdue
on act.actid = oldestdue.actid
left outer join tblpatduedate due
on act.actid = due.actid and oldestdue.duedate = due.duedate

The min(duedate) where datetaken is null will tell you the first due date, obviously, but the query can't grab the actiondue field on the same record (a min(actiondue) would give you the alphabetically first actiondue for the actid). With this, you can add the min date to the join of the tblpatduedate to ensure you're getting the earliest actiondue field value.

I'd want to run it and make sure my join logic wasn't behaving unexpectedly, but that *should* work.

Hope that helps!

[woodwardiocom.livejournal.com]

Thanks!