Hi All, I have to split a row in to 3 rows uisng the following criteria.
1.for any given year, i have to look back 2 years and look forward one quarter.
for example if i consider year 2017(jan1-dec31) my window would be JAN1,2016 through Mar31,2018.
create table test(id varchar2(8),beg_date date,end_date date);
Insert into TEST (ID,BEG_DATE,END_DATE) values ('AAA',to_date('01-AUG-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('31-DEC-9999 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST (ID,BEG_DATE,END_DATE) values ('AAA',to_date('01-JAN-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('30-JUN-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST (ID,BEG_DATE,END_DATE) values ('BBB',to_date('01-AUG-2001 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('31-DEC-9999 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST (ID,BEG_DATE,END_DATE) values ('CCC',to_date('01-AUG-2001 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('01-AUG-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST (ID,BEG_DATE,END_DATE) values ('DDD',to_date('01-AUG-2001 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('01-AUG-2015 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST (ID,BEG_DATE,END_DATE) values ('EEE',to_date('01-JAN-2018 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('31-DEC-9999 00:00:00','DD-MON-YYYY HH24:MI:SS'));
For the above data, i want to show the out put as below
1.first i want to cap the beg and end dates with my window dates of jan1,2016 through Mar31,2018.
2.I want to split the record in to 3 rows
a.for the 2 year look back
b. for the actual reporting dates of Jan1,2017 through dec31 ,2017.
c.90 day look forward
3. i also want to add an indicator Y if those dates are in my reporting year.
id beg_dt end_dt rpt_ind
AAA 01-JAN-17 00:00:00 30-JUN-17 00:00:30 Y
AAA 01-AUG-17 00:00:00 31-DEC-17 00:00:00 Y
AAA 01-JAN-18 00:00:00 31-MAR-18 00:00:00 N
BBB 01-JAN-15 00:00:00 31-DEC-16 00:00:00 N
BBB 01-JAN-17 00:00:00 31-DEC-17 00:00:00 Y
BBB 01-JAN-18 00:00:00 31-MAR-18 00:00:00 N
CCC 01-JAN-15 00:00:00 31-DEC-16 00:00:00 N
CCC 01-JAN-17 00:00:00 01-AUG-17 00:00:00 Y
DDD 01-JAN-15 00:00:00 01-AUG-15 00:00:00 N
EEE 01-JAN-18 00:00:00 31-MAR-18 00:00:00 N
something like this but i am trying to find a more efficient query.
select * from (with data
as
(select level-1 l from dual connect by level <= 3)
select id, l,
case when l=0 and beg_date < to_date('01/01/2015','mm/dd/yyyy') then to_date('01/01/2015','mm/dd/yyyy')
when l=1 --and to_date('01/01/2017','mm/dd/yyyy') between beg_date and end_date
then greatest(beg_date,to_date('01/01/2017','mm/dd/yyyy'))
when l=2 and end_date > to_date('12/31/2017','mm/dd/yyyy') then to_date('01/01/2018','mm/dd/yyyy')
end beg_dt,
case when l=0 then least(to_date('12/31/2016','mm/dd/yyyy'),end_date)
when l=1 then least(end_date,to_date('12/31/2017','mm/dd/yyyy'))
when l=2 then least( to_date('03/31/2018','mm/dd/yyyy'),end_date) end end_dt,
case when l=1 then 'Y' else 'N' end rpt_ind
from test, data)
where beg_dt is not null and beg_dt<end_dt
order by 1, 2;