*Edit Question to reflect true output, see comments.
I have the below data, I need previous program.
TableA
StartDate EndDate Program Id
1/26/15 2/23/15 Red 1
2/24/15 3/31/17 Yellow 1
5/3/16 6/1/17 Silver 1
4/1/17 1/31/18 Orange 1
2/1/18 Blue 1
MyOutput(incorrect)
StartDate EndDate Program Prev_program
1/26/15 2/23/15 Red
2/24/15 3/31/17 Yellow Red
5/3/16 6/1/17 Silver Yellow
4/1/17 1/31/18 Orange Silver
2/1/18 Blue Orange
ExpectedOutput:
StartDate EndDate Program Prev_program
1/26/15 2/23/15 Red
2/24/15 3/31/17 Yellow Red
5/3/16 6/1/17 Silver Red
4/1/17 1/31/18 Orange Yellow
2/1/18 Blue Orange
I would like to take the previous program when previous program end date is not greater than current startdate.
I used Lag which is producing results that I do not want. Lag is not taking into account "program end date is not greater than current startdate."
SELECT *
,LAG (PROGRAM, 1) OVER (PARTITION BY ID ORDER BY STARTDATE) AS PREV_PROGRAM
FROM TABLEA
Here is one way to do this. Not the most elegant or efficient, but it does the job. Two indexes, one on (id, startdate) and one on (id, enddate) may help with performance (worth testing, anyway). You are missing the id column in the output, but I assume it plays a role (and you want the processing to be done separately for each id). I wrote the query to work separately for each id, even though the test data has only one id.
The with clause is not part of the query - I included it at the top instead of creating an actual table. You don't need it - start from SELECT a1.startdate...
with
table_a ( startdate, enddate, program, id ) as (
select date '2015-01-26', date '2015-02-23', 'Red' , 1 from dual union all
select date '2015-02-24', date '2017-03-31', 'Yellow', 1 from dual union all
select date '2016-03-05', date '2017-06-01', 'Silver', 1 from dual union all
select date '2017-04-01', date '2018-01-31', 'Orange', 1 from dual union all
select date '2018-02-01', null , 'Blue' , 1 from dual
)
select a1.startdate, a1.enddate, a1.program, a1.id,
min(a2.program) keep (dense_rank last order by a2.startdate) as prev_program
from table_a a1 left outer join table_a a2
on a1.id = a2.id and a1.startdate > a2.enddate
group by a1.startdate, a1.enddate, a1.program, a1.id
;
STARTDATE ENDDATE PROGRAM ID PREV_PROGRAM
---------- ---------- -------- -- ------------
1/26/2015 2/23/2015 Red 1
2/24/2015 3/31/2017 Yellow 1 Red
3/5/2016 6/1/2017 Silver 1 Red
4/1/2017 1/31/2018 Orange 1 Yellow
2/1/2018 Blue 1 Orange