CODEWITHSUNDEEP
sql-serversas
Ramu
Ramu

Reputation: 165

Compare a row with previous 12 values

I am trying to compare a value with previous 12 months/12 rows. If the value is yes in last 12 months then yes for the 12th row. Likewise for each and every row (compare previous 12 values).

I am doing it in following way. Its working but I have to do it for multiple columns, so code is going to be very lengthy.

Is there any way we can do it in simple step for n number of columns?

select *,
--Identify customers who have applied for principal relief within the past 12 months
 lag(PrincipalReliefFlag,1)  over(partition by id,productcode order by snapshot_date) PRflg1
,lag(PrincipalReliefFlag,2)  over(partition by id,productcode order by snapshot_date) PRflg2
,lag(PrincipalReliefFlag,3)  over(partition by id,productcode order by snapshot_date) PRflg3
,lag(PrincipalReliefFlag,4)  over(partition by id,productcode order by snapshot_date) PRflg4
,lag(PrincipalReliefFlag,5)  over(partition by id,productcode order by snapshot_date) PRflg5
,lag(PrincipalReliefFlag,6)  over(partition by id,productcode order by snapshot_date) PRflg6
,lag(PrincipalReliefFlag,7)  over(partition by id,productcode order by snapshot_date) PRflg7
,lag(PrincipalReliefFlag,8)  over(partition by id,productcode order by snapshot_date) PRflg8
,lag(PrincipalReliefFlag,9)  over(partition by id,productcode order by snapshot_date) PRflg9
,lag(PrincipalReliefFlag,10) over(partition by id,productcode order by snapshot_date) PRflg10
,lag(PrincipalReliefFlag,11) over(partition by id,productcode order by snapshot_date) PRflg11
into #test
from #temp

select *
,case when (PrincipalReliefFlag='Y' or PRflg1='Y' or PRflg2='Y' or PRflg3='Y' or PRflg4='Y' or PRflg5='Y' or PRflg6='Y' or PRflg7='Y' or PRflg8='Y' or PRflg9='Y' or PRflg10='Y' or  PRflg11='Y')
    then  'Y' 
    when (PrincipalReliefFlag='N' 
                                and (PRflg1='N' or PRflg1 is null)  
                                and (PRflg2='N' or PRflg2 is null) 
                                and (PRflg3='N' or PRflg3 is null) 
                                and (PRflg4='N'  or PRflg4 is null)
                                and (PRflg5='N' or PRflg5 is null)
                                and (PRflg6='N' or  PRflg6 is null)
                                and (PRflg7='N' or PRflg7 is null)
                                and (PRflg8='N' or  PRflg8  is null)
                                and (PRflg9='N' or PRflg9 is null)
                                and (PRflg10='N' or PRflg10 is null)
                                and (PRflg11='N' or PRflg11 is null) )
    then 'N'
    end PrincipalRelief_applied_in_last12m
from #test

Expecting simple step. tried using joins as well but could not make it.

Upvotes: 0

Views: 155

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Is this what you want?

select t.*,
       max(PrincipalReliefFlag) over (partition by id, productcode
                                      order by snapshot_date
                                      rows between 11 preceding and current row
                                     ) as PrincipalReliefFlag_12
from #temp t;

This takes the maximum value over the 12-month period -- and will work if the flag takes on only the values of 'Y' and 'N'.

Upvotes: 2

Related Questions