divya.trehan573 divya.trehan573 - 1 year ago 83
SQL Question

How to compare old and previous row and get changed data only in sql using Lag

I have a table xx_asg with structure :

person_id grade_id effective_start_date effective_end_date
1 null 28-Jan-97 28-Jan-16
1 35 29-Jan-16 31-Dec-4712
6 35 12-Jun-93 31-Jul-93
6 35 01-Aug-93 30-Sep-99

I have to find out if there is any change in grade_id by comparing previous row with the next row.
If there is a change then i have to fetch a new column with flag 'Y'
and also the new grade's effective_start_date. I have tried to create the following query :

From (
Select Person_id,
LAG(grade_id) OVER (PARTITION BY person_ID ORDER BY effective_start_Date) AS prev_grade_line1,
Row_Number() Over (Partition By Person_Id Order By Effective_Start_Date Desc) As Rn,
From xx_asg
--WHERE person_ID = 3
Where Rn = 1
order by person_id


But this query is also returning prev_grade_line1 and new grade id as null or the same :

enter image description here

Output should look like :

person_id grade_id prev_grade_id effective_start_date Flag
1 null 35 29-Jan-97 Y
6 35 35 NULL NULL


person_id grade_id prev_grade_id effective_start_date Flag
1 null 35 29-Jan-97 Y

When using :

enter image description here

This query is returning the frst row as well. That is it treating the previous ggrade of frst row as null. Only 3 changes are there in real but this query is returning four chanegs

enter image description here

vkp vkp
Answer Source

Use a where condition to only get the required rows after getting the previous row's value.

select t.*, 'Y' flag
from (
LAG(grade_id) OVER(PARTITION BY person_ID ORDER BY effective_start_Date) prev_grade_line1,
row_Number() Over(Partition By Person_Id Order By Effective_Start_Date) As rn
from xx_asg
) t
where nvl(grade_id,10000000) <> nvl(prev_grade_line1,10000000) 
and rn > 1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download