sql - Calculate calendar days by month for date ranges

Question

I have a table with date ranges:

CREATE TABLE REQUEST (
    REQUEST_ID NUMBER(*,0) NOT NULL ENABLE,
    EMPLOYEE_ID NUMBER(*,0) NOT NULL ENABLE,
    START_DATE DATE NOT NULL ENABLE,
    END_DATE DATE NOT NULL ENABLE,
    CONSTRAINT REQUEST_PK PRIMARY KEY (REQUEST_ID)
);

The're also a couple of constraints (omitted for brevity) that ensure they're valid (end date cannot be less then start date) and force time to be 00:00:00. _{(Runnable fiddle with sample data)}.

Is there a way to split my data set by year/month into a result set based on the date ranges? The rules are:

• A request splits in as many rows as months its range covers.
• Each month has a column stating how many calendar days the range has in such month.

For example, a request with [2020-12-28, 2021-02-10] would produce three rows:

request_id year month days
========== ==== ===== ====
         1 2020    12    4
         1 2021     1   31
         1 2021     2   10

I've been playing with CONNECT BY but I've been unable to adapt it to my use case. Is that the right tool?

Answer 1

A more direct way to do the computation is to use connect by to generate just the needed months (not every day in every interval) - and then to do the day computation directly, rather than by counting. Something like this:

Adding data for testing:

insert into request (request_id, employee_id, start_date, end_date)
    select 1, 1001, date '2020-12-28', date '2021-02-10' from dual union all
    select 2, 4002, date '2021-02-10', date '2021-02-20' from dual union all
    select 3, 6004, date '2020-12-15', date '2021-03-31' from dual
;

commit;

Query and output:

with
  prep (request_id, start_date, end_date, mth) as (
    select  request_id, start_date, end_date, 
            add_months(trunc(start_date, 'mm'), level - 1)
    from    request
    connect by  level <= months_between(trunc(end_date, 'mm'),
                                        trunc(start_date, 'mm')) + 1
            and prior request_id = request_id
            and prior sys_guid() is not null
  )
select request_id, extract(year from mth) as year_,
       extract(month from mth) as month_,
       least(last_day(mth), end_date) - greatest(mth, start_date) + 1 as days
from   prep
order  by request_id, mth  --  if needed
;


REQUEST_ID      YEAR_     MONTH_       DAYS
---------- ---------- ---------- ----------
         1       2020         12          4
         1       2021          1         31
         1       2021          2         10
         2       2021          2         11
         3       2020         12         17
         3       2021          1         31
         3       2021          2         28
         3       2021          3         31