Hello experts,
I was given the two wonderful solutions to the problem posted
undefined (0 Bytes)
I would like to add another attribute called "Non_monthend_cnt".
The additional attribute should be based on the following requirement,
if the month falls within the date range then show it
hence, based on the following sample data below gotten from the link above
with
sample_data (id, start_dt, end_dt) as (
select 1001, date '2020-03-10', date '2020-12-31' from dual union all
select 2005, date '2021-12-31', null from dual union all
select 3008, date '2021-04-10', date '2021-04-30' from dual union all
select 4001, date '2021-05-12', date '2021-05-20' from dual
)
Results expected
ID each_monthend_cnt Non_monthend_cnt
---- ------------ ------------------
1001 Mar 2020 Mar 2020
1001 Apr 2020 Apr 2020
1001 May 2020 May 2020
1001 Jun 2020 Jun 2020
1001 Jul 2020 Jul 2020
1001 Aug 2020 Aug 2020
1001 Sep 2020 Sep 2020
1001 Oct 2020 Oct 2020
1001 Nov 2020 Nov 2020
2005 Dec 2021 Dec 2021
2005 Jan 2022 Jan 2022
2005 Feb 2022 Feb 2022
2005 Mar 2022 Mar 2022
3008 Apr 2021
4001 May 2021
I am still struggling to come up with a solution but I thought I should post it to get help as well.
Thanks in advance