Hi,
I am using Oracle 11.2.1 and looking for an alternative function to Connect by Level, is there an alternative to the query below to return months between start and end dates?
create table level_test
(id number,
strt_dt date,
end_dt date,
amt number,
prod varchar2(15),
category varchar2(15),
dept number
);
insert into level_test values(100, '13-JAN-2018', '31-DEC-2021', 400, 'P1', 'INT', 50);
insert into level_test values(100, '13-JAN-2018', '31-DEC-2021', 5000, 'P2', 'INT', 50);
insert into level_test values(200, '31-JULY-2019', '31-MAR-2022', 345, 'P3', 'EXT', 20);
insert into level_test values(500, '01-DEC-2016', '31-DEC-2020', 560, 'P3', 'EXT', 10);
insert into level_test values(600, '13-AUG-2019', '14-FEB-2021', 400, 'P1', 'INT', 50);
insert into level_test values(700, '13-AUG-2020', '14-nov-2020', 5000, 'P6', 'EXT', 20);
SELECT ID,
to_number(to_char(add_months(strt_dt,LEVEL -1),'YYYYMMDD') ) inv_dt,
strt_dt,
end_dt,
amt
FROM
(SELECT ID ,
strt_dt ,
end_dt ,
amt ,
prod ,
category,
dept ,
round(months_between (end_dt,strt_dt),0) tot_mths,
round(amt/round(months_between (end_dt,strt_dt),0),2) pymnt
FROM level_test
)
CONNECT BY ID = PRIOR ID
AND strt_dt = PRIOR strt_dt
AND end_dt = PRIOR end_dt
AND prod = PRIOR prod
AND category = PRIOR category
AND dept = PRIOR dept
AND amt = PRIOR amt
AND tot_mths = PRIOR tot_mths
AND pymnt = PRIOR pymnt
AND LEVEL <= tot_mths
AND PRIOR sys_guid() IS NOT NULL
Thanks