CODEWITHSUNDEEP
sqlpostgresqlamazon-redshift
izzy84
izzy84

Reputation: 259

How to identify MIN value for records within a rolling date range in SQL

I am trying to calculate a MIN date by Patient_ID for each record in my dataset that dynamically references the last 30 days from the date (Discharge_Dt) on that row. My initial thought was to use a window function, but I opted for a subquery, which is close, but not quite what I need.

Please note, my sample query is also missing logic that limits the MIN Discharge_Dt to the last 30 days, in other words, I do not want a MIN Discharge_Dt that is older than 30 days for any given row.

Sample Query:

SELECT Patient_ID,
       Discharge_Dt,

      /* Calculating the MIN Discharge_Dt by Patient_ID for the last 30 
         days based upon the Discharge_Dt for that row */

      (SELECT MIN(Discharge_Dt)
       FROM admissions_ds AS b
       WHERE a.Patient_ID = b.Patient_ID AND
             a.Discharge_Dt >= DATEADD('D', -30, GETDATE())) AS MIN_Dt

FROM admissions_ds AS a

Desired Output Table:

Patient_ID | Discharge_Dt | MIN_Dt      
10         | 2017-08-15   | 2017-08-15         
10         | 2017-08-31   | 2017-08-15          
10         | 2017-09-21   | 2017-08-31          
15         | 2017-07-01   | 2017-07-01         
15         | 2017-07-18   | 2017-07-01               
20         | 2017-05-05   | 2017-05-05          
25         | 2017-09-24   | 2017-09-24

Upvotes: 0

Views: 818

Answers (1)

Jon Scott
Jon Scott

Reputation: 4354

Here you go, Just a simple join required.

drop TABLE if EXISTS admissions_ds;
create table admissions_ds (Patient_ID int,Discharge_Dt date);
insert into admissions_ds
values
  (10,'2017-08-15'),
  (10,'2017-08-31'),
  (10,'2017-09-21'),
  (15,'2017-07-01'),
  (15,'2017-07-18'),
  (20,'2017-05-05'),
  (25,'2017-09-24');

select t1.Patient_ID,t1.Discharge_Dt,min(t2.Discharge_Dt) as min_dt
from admissions_ds as t1
join admissions_ds as t2 on t1.Patient_ID=t2.Patient_ID and t2.Discharge_Dt > t1.Discharge_Dt - interval '30 days'
group by 1,2
order by 1,2
;

Upvotes: 1

Related Questions