CODEWITHSUNDEEP
sqlsql-server
drew
drew

Reputation: 481

How can I group rows by most recent previous date from a column in another table?

If I have a table md_visits:

+------------+--------------------+
| patient_id | doctor_visit_dttm  |
+------------+--------------------+
|          1 | 2008-1-15 00:00:00 |
|          1 | 2008-6-15 00:00:00 |
|          1 | 2009-3-1 00:00:00  |
|          2 | 2008-4-1 00:00:00  |
|          2 | 2009-4-1 00:00:00  |
|          3 | 2010-1-1 00:00:00  |
|          3 | 2010-4-1 00:00:00  |
|          3 | 2010-7-1 00:00:00  |
+------------+--------------------+

that I want to join to table food_journal
on food_journal.patient_id = md_visits.patient_id :

+------------+---------------------+---------------+
| patient_id |      eat_dttm       |     food      |
+------------+---------------------+---------------+
|          1 | 2008-1-20 00:00:00  | burger        |
|          1 | 2008-1-20 00:00:00  | fries         |
|          1 | 2008-8-15 00:00:00  | lasagna       |
|          1 | 2008-9-15 00:00:00  | chicken wings |
|          1 | 2008-10-15 00:00:00 | burger        |
|          1 | 2009-5-15 00:00:00  | pizza         |
|          2 | 2008-6-15 00:00:00  | pizza         |
|          2 | 2008-6-25 00:00:00  | pizza         |
|          2 | 2008-6-25 00:00:00  | salad         |
|          2 | 2009-1-15 00:00:00  | fries         |
|          2 | 2009-8-15 00:00:00  | pasta         |
|          3 | 2011-1-15 00:00:00  | chicken wings |
|          3 | 2011-3-15 00:00:00  | salad         |
|          3 | 2012-1-15 00:00:00  | burger        |
|          3 | 2012-3-15 00:00:00  | fries         |
+------------+---------------------+---------------+

how can I get a table like this:

+------------+--------------------+---------------------+---------------+
| patient_id | doctor_visit_dttm  |      eat_dttm       |     food      |
+------------+--------------------+---------------------+---------------+
|          1 | 2008-1-15 00:00:00 | 2008-1-20 00:00:00  | burger        |
|          1 | 2008-1-15 00:00:00 | 2008-1-20 00:00:00  | fries         |
|          1 | 2008-6-15 00:00:00 | 2008-8-15 00:00:00  | lasagna       |
|          1 | 2008-6-15 00:00:00 | 2008-9-15 00:00:00  | chicken wings |
|          1 | 2008-6-15 00:00:00 | 2008-10-15 00:00:00 | burger        |
|          1 | 2009-3-1 00:00:00  | 2009-5-15 00:00:00  | pizza         |
|          2 | 2008-4-1 00:00:00  | 2008-6-15 00:00:00  | pizza         |
|          2 | 2008-4-1 00:00:00  | 2008-6-25 00:00:00  | pizza         |
|          2 | 2008-4-1 00:00:00  | 2008-6-25 00:00:00  | salad         |
|          2 | 2008-4-1 00:00:00  | 2009-1-15 00:00:00  | fries         |
|          2 | 2009-4-1 00:00:00  | 2009-8-15 00:00:00  | pasta         |
+------------+--------------------+---------------------+---------------+
...

where all the rows are grouped so that eat_dttm is correlated to only the most recent previous doctor_visit_dttm (food eaten since last doctor's visit)?

I know I could do WHERE eat_dttm > doctor_visit_dttm but this would include ALL the previous visits, not just the most recent one.

Upvotes: 1

Views: 56

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

I would recommend outer apply:

select md.*, fj.food
from md_visits md outer apply
     (select top (1) fj.*
      from food_journal fj
      where md.patient_id = fj.patient_id and
            md.doctor_visit_dttm < fj.eat_dttm
      order by fj.eat_dttm asc
     ) fj
order by 1, 2;

Here is a db<>fiddle.

Upvotes: 1

Cetin Basoz
Cetin Basoz

Reputation: 23797

You can do it like this:

WITH visits (patient_id, doctor_visit_dttm, nextVisit)
AS (SELECT patient_id,
           doctor_visit_dttm,
           LEAD(doctor_visit_dttm) OVER (PARTITION BY patient_id ORDER BY doctor_visit_dttm)
    FROM md_visits)
SELECT v.patient_id,
       v.doctor_visit_dttm,
       fj.eat_dttm,
       fj.food
FROM visits v
    INNER JOIN dbo.food_journal fj
        ON fj.patient_id = v.patient_id
           AND fj.eat_dttm >= v.doctor_visit_dttm
           AND
           (
               v.nextVisit IS NULL
               OR fj.eat_dttm < v.nextVisit
           );

EDIT: Here is DBFiddle demo

Upvotes: 1

Related Questions