CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Álvaro González
Álvaro González

Reputation: 146390

Calculate calendar days by month for date ranges

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:

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?

Upvotes: 0

Views: 97

Answers (2)

user5683823
user5683823

Reputation:

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

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59446

For example this one:

WITH t AS (
    SELECT DATE '2020-12-28' +LEVEL-1 AS ts
    FROM dual 
    CONNECT BY  DATE '2020-12-28' +LEVEL-1 <= DATE '2021-02-10')
SELECT 
    EXTRACT(YEAR FROM TRUNC(ts, 'Month')) AS YEAR, 
    EXTRACT(MONTH FROM TRUNC(ts, 'Month')) AS MONTH, 
    COUNT(ts) AS DAYS 
FROM t
GROUP BY TRUNC(ts, 'Month')
ORDER BY YEAR, MONTH;

Upvotes: 0

Related Questions