How can I select a max date from a selection of dates less than another table's max?

Question

Question:

How can I select the max(d1_10.dstartdate) (latest unit transfer date) for every xpid (patient ID) from a selection of dates in d1_10 that are less than each xpid's max(d8.start_date) (latest session date)?

Info:

Tables:

• d0 Patient List Table, contains 1 row per xpid (patient)
  • d0.xpid Unique Patient ID
• d8 Session Table, contains 1 row per session per xpid
  • d8.xpid Patient ID
  • d8.start_date Session Start Date
• d1_10 Unit Transfer Table, contains 1 row per transfer per xpid
  • d1_10.dstartdate Transfer Date

Problem:

Currently I have a query that lists xpid, max(d8.start_date), and max(d1_10.startdate). But, max(d1_10.startdate) isn't necessarily less than max(d8.start_date)

The goal is to have a list of all patients, their latest session date, and their latest transfer date that was still prior to their session date. Currently transfer date is independent of session date.

I have tried nesting the join queries, but haven't been able to figure out the scope of calling the variables.

Progress:

This is the closest running code I have:

select distinct
    d0.xpid,
    sessions.max_date,
    units.max_unit
from d0
    left join
        (
            select xpid, max(start_date) as max_date
            from d8
            group by xpid
        ) as sessions on sessions.xpid = d0.xpid
    left join
        (
            select xpid, max(d1_10.dstartdate) as max_unit
            from d1_10
            group by xpid
        ) as units on units.xpid = d0.xpid

Snippet from Output:

Note the null values, these are good, we want these. We want one row of output for every patient in d0 regardless of their rows in d8 or d1_10.

Thanks in advance, please let me know if I can provide more clarity.

Additional Info:

• Tried adding and units.max_unit <= sessions.max_date as suggested by Sebastian. It's close, but instead of restricting by max_unit <= max_date, we need that all d1_10.dstartdate <= sessions.max_date before taking the max. We want it to restrict the pool it picks units.max_unit from before taking the maximum.

• @Sebastian, Suppose xpid = 2500 has 3 rows in d1_10 with d1_10.dstartdate in ('01-jan-2017', '01-feb-2017', '01-mar-2017') and max(d8.start_date) = '10-feb-2017'.

  With your suggestion, max_unit is null, because the max(d1_10.dstartdate) = '01-mar-2017', but '01-mar-2017 > '10-feb-2017'.

  We need to restrict d1_10.dstartdate <= max(d8.start_date) before taking the maximum. The desired value should be max_unit = '01-feb-2017' because this is the maximum transfer date for this patient that is less than this patient's maximum session date. Let me know if I can clear it up better.

Answer 1

Adding

AND d1_10.max_unit < sessions.max_date 

to the on clause of the second left join is enough.

After your edit, please try that one:

SELECT  d0.xpid,
        d8.[start_date],
        d1_10.dstartdate
FROM d0
LEFT JOIN d8
    ON d0.xpid = d8.xpid
LEFT JOIN d1_10
    ON d1_10.xpid = d8.xpid
    AND d1_10.dstartdate = (
                                SELECT MAX(u1.dstartdate)
                                FROM d1_10 as u1
                                WHERE d8.xpid = u1.xpid 
                                AND u1.dstartdate <= d8.start_date
                                GROUP BY u1.xpid
                            )