CODEWITHSUNDEEP
sqlsql-server
I.m.rich
I.m.rich

Reputation: 55

How to lag over multiple months in SQL

I currently have a table that looks like this 

|DataDt|ID|X|Y|  
|1.1.10|1 |C|7|  
|2.1.10|1 |C|5|  
|3.1.10|1 |C|4|  
|4.1.10|1 |C|8|  
|5.1.10|1 |C|1|  
|6.1.10|1 |3|2|  
|7.1.10|1 |6|7|  
|8.1.10|1 |C|7|  
|1.1.10|2 |C|7|  
|2.1.10|2 |C|7|  
|3.1.10|2 |3|7|  
|4.1.10|2 |C|7|

I want to track if there has been a time where X != C at any point in the previous 12 months, and flag it

|DataDt|ID|X |Y|Flag   
|1.1.10|1 |C |7|0  
|2.1.10|1 |C |5|0  
|3.1.10|1 |C |4|0  
|4.1.10|1 |C |8|0  
|5.1.10|1 |C |1|0  
|6.1.10|1 |3 |2|0  
|7.1.10|1 |6 |7|1  
|8.1.10|1 |C |7|1  
|1.1.10|2 |C |7|0  
|2.1.10|2 |C |7|0  
|3.1.10|2 |3 |7|0  
|4.1.10|2 |C |7|1

Normally I use the following code to lag the previous month:

  ;select ID, datadt, X, flag into X_Table from Data
(
    Select  loan_num, datadt, X, flag,
            Lag(X) Over (Partition By ID Order By datadt Asc) As Prev
    From    X_Table 
) 
Update  X_Table 
Set    flag = 1
where prev != 'C'

However that only works for one month and not 12.

Any suggestions?

Upvotes: 1

Views: 1797

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

I wouldn't use lag(), but exists:

update X_Table
    set flag = 1
    where exists (select 1
                  from X_table x2
                  where x2.id = X_table.id and
                        x2.x <> 'C' and
                        x2.datadt > x.datadt - interval '12 month' and
                        x.datadt <= x.datadt
                 );

Note that this uses ANSI standard syntax for date arithmetic, because you have not specified the database.

You can use window functions, but lag() is not appropriate. Assuming you have a row for every month of interest (with no gaps):

with toupdate as (
      select x.*,
             sum(case when x <> 'C' then 1 else 0 end) over (partition by id order by datadt rows between 11 preceding and current row) as num_notc
      from x_table x
     )
update toupdate
    set flag = (case when num_notc > 0 then 1 else 0 end);

Upvotes: 3

Related Questions