Latest value of compared date range? (SQL/Snowflake)

I have values in Table-A like:

Patient|Invoice|Date
A,111,2021-02-01
A,222,2021-01-01
B,333,2021-03-01
B,444,2021-02-01
C,555,2021-04-01
C,666,2021-03-01

​x
 
Patient|Invoice|DateA,111,2021-02-01A,222,2021-01-01B,333,2021-03-01B,444,2021-02-01C,555,2021-04-01C,666,2021-03-01​

And values in Table-B like:

Patient|Value|Date
A,2,2021-01-05
A,3,2021-01-05
A,3,2021-02-05
B,1,2021-02-05
B,1,2021-03-05
C,6,2021-01-01

 
Patient|Value|DateA,2,2021-01-05A,3,2021-01-05A,3,2021-02-05B,1,2021-02-05B,1,2021-03-05C,6,2021-01-01​

And I want to join the two tables such that I see the most recent cumulative sum of values in Table-B as-of the Date in Table-A for a given Patient.

Patient|Invoice|Latest Value|Date
A,111,5,2021-02-01
A,222,0,2021-01-01
B,333,1,2021-03-01
B,444,0,2021-02-01
C,555,6,2021-04-01
C,666,6,2021-03-01

 
Patient|Invoice|Latest Value|DateA,111,5,2021-02-01A,222,0,2021-01-01B,333,1,2021-03-01B,444,0,2021-02-01C,555,6,2021-04-01C,666,6,2021-03-01​

How would I join these two tables by date to accomplish this?

Answer

First step seems like a basic SQL join:

select patient, invoice, sum(value), date
from table1 a
join table2 b
on a.patient=b.patient
and a.date=b.date
group by patient, invoice, date

 
select patient, invoice, sum(value), datefrom table1 ajoin table2 bon a.patient=b.patientand a.date=b.dategroup by patient, invoice, date​

But instead of a plain sum() you can apply a sum() over():

select patient, invoice
  , sum(value) over(partition by patient order by date)
  , date
from table1 a
join table2 b
on a.patient=b.patient
and a.date=b.date
group by patient, invoice, date

 
select patient, invoice  , sum(value) over(partition by patient order by date)  , datefrom table1 ajoin table2 bon a.patient=b.patientand a.date=b.dategroup by patient, invoice, date​

Advertisement

Answer