Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

adding another count to this solution given

user13328581May 20 2022

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
This post has been answered by Frank Kulash on May 24 2022
Jump to Answer

Comments

Post Details

Added on May 20 2022
11 comments
184 views
3 mentions