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 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