Given Table A, where the rows are each date enumerated for each ID (it should be comprehensive, i.e ID=1 doesn't stop at 1/4/2020 like shown here, but rather it goes all the way until 2022):

and Table B of special dates for a given ID:

How would I create a new table that has for each row in table A, the most recent special date from table B? e.g

I imagine you'd have to join on both ID and date, but I'm not sure how to proceed from there. Thank you!

CodePudding user response：

Join the tables and use MAX().

SELECT A.id, A.date, MAX(B.date) AS most_recent_special_date
FROM A
JOIN B ON A.id = B.id AND A.date > B.date
GROUP BY A.id, A.date

CodePudding user response：

Barmar's answer works nicely and fast, if you only want the single value from table_b, if there are other values you want from table_b moving the MAX into a QUALIFY allows retrieving all values from that row:

so with some CTE's for the data:

with table_a(ID,Date) as (
    select column1, to_date(column2, 'mm/dd/yyyy') from values
    (1, '1/1/2020'),
    (1, '1/2/2020'),
    (1, '1/3/2020'),
    (1, '1/4/2020'),
    (2, '1/1/2020'),
    (2, '1/2/2020'),
    (2, '4/2/2020')
), table_b(ID,Date) as (
    select column1, to_date(column2, 'mm/dd/yyyy') from values
   (1, '12/3/2019'),
   (1, '1/2/2020'),
   (1, '2/5/2020'),
   (1, '1/10/2020'),
   (2, '2/10/2020'),
   (2, '4/11/2020') 
)

we also have a difference in out joins depends if you want the most recent including today (the way I have done it >=), or not including today (the way Barmar did it >).

select 
    a.ID,
    a.Date,
    b.date as "Most Recent Special Date"
from table_a as a
left join table_b as b
    on a.id = b.id and a.date >= b.date
qualify b.date = max(b.date) over (partition by a.id, a.date);

gives: