How to Use window function to get minimum value for bunch of groups but grouped in inplace sorted manner

0
votes

I have a table say customer

DATE ID TYPE
2018-01-01 1 FI
2019-01-01 1 LF
2020-01-01 1 LF
2021-01-01 1 FI
2022-01-01 1 LF

Now what I want to do is add a new column ' NEW_DATE ' in which I have a logic as follows:

if type = FI then set it NULL
Else if LF then take the lowest Date which is following the continuous order

Expected output:

DATE ID TYPE NEW_DATE
2018-01-01 1 FI NULL
2019-01-01 1 LF 2019-01-01
2020-01-01 1 LF 2019-01-01
2021-01-01 1 FI NULL
2022-01-01 1 LF 2022-01-01

In this the2nd and 3rd row have continuous LF code so it will take the lowest date i.e, 2019-01-01 and in the 4rth FI is there so null and chain breaks. So 5th Row gets 2022-01-01

Now I want to use window function like

CASE 
    WHEN TYPE <> 'LF' 
        THEN NULL
        ELSE MIN(DATE) OVER (PARTITION BY TYPE ORDER BY DATE) 
END AS NEW_DATE

but this considers all types as one whole. So what can be the solution to this?

2
sqlsql-serverteradatawindow-functions
SQL Server or Teradata? - Andrew
Any will work..! - Ishaan
can you share the results of your query - trillion
FI all null and 2019-01-01 in all LF as its taking all LF as one group and giving the Minimum Date. But what I want is just to take one bunch of LF groups and give me the Min Date for that Bunch If it makes sense - Ishaan
you mean two consecutives LF should have same minimum date but no the later one - trillion

2 Answers

1
votes

I think I understand what you need, you need to define a group for each block of types and then get the minimum date per block:

This is a type of gaps-and-islands problem. In the CTE we assign an incrementing row number for only type LF, and subtract from it a row number for all rows, this results in the same value for consecutive types. This then provides the mechanism to partition/group by in order to get the minimum date per block of consecutive types.

with grouped as (
select id, date, type, 
     case when type='LF' then Row_Number() over (partition by id, type  order by date) end -Row_Number()  over (partition by id order by date) gp
from customer
)
select date, id, type, 
    case when type='LF' then Min(date) over(partition by gp) end New_Date
from grouped
order by date
0
votes

I think you want:

(CASE WHEN TYPE = 'LF' 
      THEN MIN(CASE WHEN TYPE = 'FI' THEN DATE END) OVER (PARTITION BY ID ORDER BY DATE DESC) 
 END) AS NEW_DATE

I added the PARTITION BY ID because I'm guessing that you want this per ID. If not, then remove the PARTITION BY clause.

Here is a db<>fiddle.

EDIT:

I misunderstood the question. You seem to want:

select t.*,
       (case when type = 'LF'
             then max(case when type = 'LF' and (prev_type is null or prev_type <> type)
                           then date
                      end) over (partition by id order by date)
        end) as lf_date
from (select t.*,
             lag(type) over (partition by id order by date) as prev_type
      from t
     ) t