Query to find changes in a row wrt previous row in SQL query

Question

I have a table per_all_Assignments_f with date_from and date_to and following column structure :

PERSON_ID     DATE_FROM         DATE_TO      GRADE
---------    ------------      -----------   -----
12            01-Jan-2018      28-Feb-2018     c
12            01-Mar-2018      29-Mar-2018     a
12            30-Mar-2018      31-dec-4712     b
13            01-jan-2018      31-dec-4712     c

In the above table, I have to retrieve the latest grade change i.e. for person_id '12', I have to retrieve both record rows : 30-mar-2018 to 31 dec 4712 being the latest and one prior row. What function can i use for this ?

solved by :

SELECT person_id,
       asg.grade_id,
       lag(asg.grade_id) Over (Partition By person_ID Order By start_date) as prev_ppg_line1,
       lag(start_date) Over (Partition By person_ID Order By start_date) 
                                                            as prev_ppg_effective_start_date,
       start_date,
       row_Number() Over (Partition By person_ID Order By effective_start_date) as rn
  FROM asg_table asg
 WHERE person_id = 12;

This query will fetch 3 rows with all the previous changes. I want to fetch the latest change only without using max on effective start date

you mean select person_id , date_From , date_to , grade from per_all_Assignments_f order by date_From date_to desc — Moudiz, Dec 26 '18 at 10:11
no moudiz i mean if the grade has changed then the latest row and 1 previous row. so for the above example.... row 2 and 3 should be returned for person 12. because from 30-march to current the grade has changed from a to b — SSA_Tech124, Dec 26 '18 at 10:12

score 3 · Accepted Answer · answered Dec 26 '18 at 10:52

3

You can use row_number and lead analytic functions together inside the subquery as :

select person_id, date_From, date_to, grade
  from
  (
    with per_all_Assignments_f(person_id, date_From, date_to, grade) as
    (
     select 12,date'2018-01-01',date'2018-02-28','c' from dual union all
     select 12,date'2018-03-01',date'2018-03-29','a' from dual union all
     select 12,date'2018-03-30',date'4172-12-31','b' from dual union all
     select 13,date'2018-01-01',date'4172-12-31','c' from dual  
    )
    select t.*,
           lead(grade) over (order by date_From desc) as ld,
           row_number() over (order by date_From desc) as rn 
      from per_all_Assignments_f t
  )   
  where rn <= 2
    and grade != ld
  order by rn desc;

 PERSON_ID  DATE_FROM    DATE_TO    GRADE
 ---------- ----------- ---------- -------
     12     01.03.2018  29.03.2018    a
     12     30.03.2018  31.12.4172    b

Rextester Demo

answered Dec 26 '18 at 10:52

Barbaros Özhan

59,113
10
31
55

Thanks.. ive created a query to solve the above query using lag. What can i use to get the latest row or the max effective start date row only ? – SSA_Tech124 Dec 26 '18 at 11:53
1

@Sree . . . This answers the question that you asked. If you have another question, then ask as another question. – Gordon Linoff Dec 26 '18 at 12:09
@Sree you're welcome. Nice to hear if I could help you. As Gordon pointed out, it's nice to ask another question and state exactly what you want. By the way, kindly please mark the answer, if helped. – Barbaros Özhan Dec 26 '18 at 12:30
hey i ahve edited and added the solution i have adopted with the latest related question – SSA_Tech124 Dec 26 '18 at 13:30

score 1 · Answer 2 · answered Dec 26 '18 at 10:36

Seems like you just want all with a row_number() of 1 or 2 partitioned by the person and ordered by the beginning descending.

SELECT person_id,
       date_from,
       date_to,
       grade
       FROM (SELECT person_id,
                    date_from,
                    date_to,
                    grade,
                    row_number() OVER (PARTITION BY person_id
                                       ORDER BY date_from DESC) rn
                    FROM per_all_assignments_f t) x
       WHERE rn IN (1, 2)
       ORDER BY person_id ASC,
                date_from DESC;

Query to find changes in a row wrt previous row in SQL query

2 Answers2