Calculate the number of days per month between two dates

Question

Using Oracle 12c, I need to run a script on an existing summary table of projects. The summary table has a project, a start date, and an end date. I need to break this data out into the number of days per month for each project.

An example is Project A has a start date of 2/10/2016 and an end date of 3/10/2016. My ending result for this example should be:

Project A, February, 19
Project A, March, 10

This was an easier one as some dates may span 2 or 3 months. This doesn't seem too difficult but for some reason I'm having trouble wrapping my head around it and overthinking it. Does someone have an quick and easy solution to this? I would like to run this as a SQL statement but a PL/SQL script would also work.

Answer 1

I think you're after something like:

WITH sample_data AS (SELECT 'A' PROJECT, to_date('10/02/2016', 'dd/mm/yyyy') start_date, to_date('10/03/2016', 'dd/mm/yyyy') end_date FROM dual UNION ALL
                     SELECT 'B' PROJECT, to_date('10/02/2016', 'dd/mm/yyyy') start_date, to_date('10/06/2016', 'dd/mm/yyyy') end_date FROM dual UNION ALL
                     SELECT 'C' PROJECT, to_date('10/02/2016', 'dd/mm/yyyy') start_date, to_date('18/02/2016', 'dd/mm/yyyy') end_date FROM dual)
SELECT PROJECT,
       to_char(add_months(trunc(start_date, 'mm'), LEVEL -1), 'fmMonth yyyy', 'nls_date_language=english') mnth,
       CASE WHEN trunc(end_date, 'mm') = add_months(trunc(start_date, 'mm'), LEVEL -1)
                 THEN end_date
            ELSE add_months(trunc(start_date, 'mm'), LEVEL) -1
       END - CASE WHEN trunc(start_date, 'mm') = add_months(trunc(start_date, 'mm'), LEVEL -1)
                 THEN start_date + 1
            ELSE add_months(trunc(start_date, 'mm'), LEVEL -1)
       END + 1 num_days
FROM   sample_data
CONNECT BY PRIOR PROJECT = PROJECT
           AND PRIOR sys_guid() IS NOT NULL
           AND add_months(trunc(start_date, 'mm'), LEVEL -1) <= TRUNC(end_date, 'mm');

PROJECT MNTH             NUM_DAYS
------- -------------- ----------
A       February 2016          19
A       March 2016             10
B       February 2016          19
B       March 2016             31
B       April 2016             30
B       May 2016               31
B       June 2016              10
C       February 2016           8

This uses the multi-row connect-by-level technique (the presence of the and prior sys_guid() is not null enables the connect by to loop through each row separately) to loop through each project row in the sample_data table (you presumably have the project information in a table already, so you wouldn't need to have the sample_data subquery at all; you could just reference your table directly in the main SQL).

We then compare the month of the start date with the month of the row being generated by the connect by, and if it's the same month, then we know we need to use the start date, otherwise we use the first of the month of the generated row; we do similarly for the end date.

That way, we can now subtract one from the other and make adjustments to make the calculation correct. You may need to tweak this yourself if you need a start and end date of the same day to count as 1 day, rather than 0 - it'll probably need an extra case statement to take account of when the start and end date are in the same month.

Using this approach won't limit your project length; it could be as long as you liked.

ETA: Looks like Mathguy posted an answer whilst I was typing out my answer, and whilst our basic methods are the same, mine doesn't use an analytic function to determine the difference in the number of days. You may or may not find their answer more performant than mine - you should test both to see which one works best with your data.

Answer 2

If you can do an assumption on the maximum number of days for a project (1000 in my example), you can use the following:

with yourTable(project, startDate, endDate) as 
(
    select 'Project a' as project,
           date '2016-02-10' as startDate,
           date '2016-03-10' as endDate
    from dual
    UNION ALL
    select 'Project XX',
           date '2016-01-01',
           date '2016-01-10'
    from dual
)
select project, to_char(startDate + n, 'MONTH'), count(1)
from yourTable
  inner join (
                select level n
                from dual
                connect by level <= 1000
             )
    on (startDate + n <= endDate)        
group by project, to_char(startDate + n, 'MONTH')  

The part with the CONNECT BY is used as a date generator, assuming that every project is at maximum 1000 days long; the external query uses the date generator to split the row of a project in many rows, one for each day between start and end date, and then aggregates by month and project to build the output.

A slightly different way, based on months and not days, could be:

select project, to_char(add_months(startDate, n ), 'MONTH'),
       case
        when trunc(add_months(startDate, n ), 'MONTH') = trunc(add_months(endDate, n ), 'MONTH')
            then endDate - startDate +1
        when trunc(add_months(startDate, n ), 'MONTH') <= startDate
            then last_day(add_months(startDate, n)) - startDate
        when last_day(add_months(startDate, n )) >= endDate
            then endDate - trunc(add_months(startDate, n ), 'MONTH') +1      
        else
            last_day(add_months(startDate, n )) - trunc(last_day(add_months(startDate, n )), 'MONTH')
       end as numOfDays       
from yourTable
  inner join (
                select level -1 n
                from dual
                connect by level <= 1000
             )
    on trunc(add_months(startDate, n ), 'MONTH') <= trunc(endDate, 'MONTH') 

This is a bit more complicated, to handle the different cases, but more efficient, given that it works at month level, not day level

Answer 3

In this solution we don't assume any prior knowledge of the time period covered. Also, this solution does not use joins (which may be important for performance).

with
-- begin test data (this section can be deleted)
     inputs ( project, start_date, end_date ) as (
       select 'A', date '2014-10-03', date '2014-12-15' from dual union all
       select 'B', date '2015-03-01', date '2015-03-31' from dual union all
       select 'C', date '2015-11-30', date '2016-03-01' from dual
     ),
-- end test data; solution begins here (it includes the word "with" from the first line)
     prep ( project, end_date, dt ) as (
       select project, end_date, start_date   from inputs union all
       select project, end_date, end_date + 1 from inputs union all
       select project, end_date, add_months( trunc(start_date, 'mm'), level )
         from inputs
         connect by add_months (trunc(start_date, 'mm'), level) <= end_date
                and prior project = project
                and prior sys_guid() is not null
     ),
     computations ( project, dt, month, day_count ) as (
       select project, dt, to_char(dt, 'Mon-yyyy'),
              lead(dt) over (partition by project order by dt) - dt
       from   prep
       where  dt <= end_date + 1
     )
select project, month, day_count
from   computations
where  day_count > 0
order by project, dt
;

OUTPUT:

PROJECT MONTH    DAY_COUNT
------- -------- ---------
A       Oct-2014        29
A       Nov-2014        30
A       Dec-2014        15
B       Mar-2015        31
C       Nov-2015         1
C       Dec-2015        31
C       Jan-2016        31
C       Feb-2016        29
C       Mar-2016         1

 9 rows selected