sql - Using LAG() to dynamically look at the whole entire window

Question

I have a peice of SQL that I am using to generate this table -

patient  status  lag_start_date  thru_dt    group
10000    30      20191104        20200102   20497762
10000    30                      20200103   20497762
10000    01                      20200302   20497763

The first two are given the same group because of various conditions/case statements I have.

How do I get the third record to have the same group if the first record of a particular patient has a lag_start_date and all the other records are given the same group as the first record (e.g. Both 1 and 2 have the group of 20497762).

An example of a query that returns the above result is -

SELECT patient
         status,
         NULLIF(THRU_DT, '') THRU_DT,
           CASE
             WHEN LAG(STATUS, 1) OVER patient_window is null THEN nextval('patient_grouping')
             
             WHEN nullif(lag(start_date, 1) over patient_date_window, '')::DATE is not null 
                AND LAG(status_cd, 1) over patient_date_window  = '30' 
             then currval('patient_grouping')

            else nextval('patient_grouping')
         END as claim_group
  from claims

The output I want is

patient  status  lag_start_date  thru_dt    group
10000    30      20191104        20200102   20497762
10000    30                      20200103   20497762
10000    01                      20200302   20497762

Notice that the last record is also given the same group.

Answer 1

You can use the analytical functions first_value, count as follows:

select patient, status, lag_start_date, thru_dt, 
       case when group <> fgroup count(1) over (partition by patient) = 2  
            then fgroup 
            else group 
       end as group
  from (select t.*, 
               first_value(lag_start_date) 
                   over (partition by patient order by thru_dt) as flsd,
               first_value(group)
                   over (partition by patient order by thru_dt) as fgroup
  from (your_query) t) t