3 Replies Latest reply: Feb 9, 2013 6:43 AM by ---Brodyaga--- RSS

    Count Number of Days in Between Two Dates in Correct Month

    AustinSmith
      I'm trying to write a SELECT statement that will return the number of days spent on a project.

      Here's my problem. I have a table with multiple date columns (start_date, end_date, etc...) as well as several other columns. Here's an example of the table.

      PROJECT_ID ..... START_DATE ..... END_DATE
      123 ................. 01-JAN-13 ......... 15-JAN-13
      456 ................. 25-JAN-13 ......... 05-FEB-13
      789 ................. 30-JAN-13 ......... 05-FEB-13
      999 ................. 01-FEB-13 ......... 08-FEB-13

      I am trying to count the number of days spent on projects in each month. For example, with the data above, I would want to return...

      MONTH ....... PROJECT_DAYS
      Jan ............. 24
      Feb ............. 18

      I've tried using a CASE statement, but I'm having a difficult time figuring it out. Any help would be greatly appreciated!

      Edited by: 987079 on Feb 8, 2013 1:12 PM
        • 1. Re: Count Number of Days in Between Two Dates in Correct Month
          JustinCave
          One option would be something like
            1  with project as (
            2    select 123 project_id, date '2013-01-01' start_date, date '2013-01-15' end_date from dual union all
            3    select 456, date '2013-01-25', date '2013-02-05' from dual union all
            4    select 789, date '2013-01-30', date '2013-02-05' from dual union all
            5    select 999, date '2013-02-01', date '2013-02-08' from dual
            6  ),
            7  all_days as (
            8    select start_date + level - 1 dt
            9      from (select min(start_date) start_date,
           10                   max(end_date) end_date
           11              from project)
           12   connect by level <= end_date - start_date + 1
           13  )
           14  select trunc(dt,'MM'),
           15         count(*)
           16    from all_days ad
           17         join project p on (ad.dt between p.start_date and p.end_date)
           18   group by trunc(dt,'MM')
           19*  order by trunc(dt,'MM')
          SQL> /
          
          TRUNC(DT,'MM')        COUNT(*)
          ------------------- ----------
          2013-01-01 00:00:00         24
          2013-02-01 00:00:00         18
          Justin
          • 2. Re: Count Number of Days in Between Two Dates in Correct Month
            Frank Kulash
            Hi,

            Welcome to the forum!

            Here's one way:
            WITH   extreme_months       AS
            (
                 SELECT     TRUNC (MIN (start_date), 'MONTH')     AS first_month
                 ,     TRUNC (MAX (end_date),      'MONTH')     AS last_month
                 FROM     table_x
            )
            ,     all_months     AS
            (
                 SELECT     ADD_MONTHS (first_month, LEVEL - 1)     AS this_month
                 ,     ADD_MONTHS (first_month, LEVEL)           AS next_month
                 FROM     extreme_months
                 CONNECT BY     LEVEL <= 1 + MONTHS_BETWEEN ( last_month
                                                         , first_month
                                              )
            )
            SELECT    TO_CHAR (m.this_month, 'fmMonth, YYYY')     AS month
            ,       SUM ( LEAST    (m.next_month, x.end_date + 1)
                       - GREATEST (m.this_month, x.start_date)
                       )                              AS total_days
            FROM           all_months  m
            LEFT OUTER JOIN      table_x     x  ON   x.start_date     <  m.next_month
                                              AND  x.end_date          >= m.this_month
            GROUP BY  m.this_month
            ORDER BY  m.this_month
            ;
            If you happen to have gaps in your data (entire motnhs with no activity) the query above will show those months with NULL total_days. If you'd rather not see those months, change the LEFT OUTER JOIN in the main query to an inner JOIN.

            Edited by: Frank Kulash on Feb 8, 2013 4:42 PM
            Justin's solution is simpler and better.
            • 3. Re: Count Number of Days in Between Two Dates in Correct Month
              ---Brodyaga---
              Hi

              If you have Oracle version 11.2, you could use recursive WITH expressions
              with project as (select 123 project_id, date '2013-01-01' start_date, date '2013-01-15' end_date from dual union all
                               select 456, date '2013-01-25', date '2013-02-05' from dual union all
                               select 789, date '2012-12-30', date '2013-02-05' from dual union all
                               select 999, date '2013-02-01', date '2013-03-08' from dual),
                   pr2(start_date, end_date, n) as 
                              (select start_date, end_date, 0 from project
              
                               union all
                               
                               select start_date, end_date, n + 1
                               from pr2
                               where n < end_date - start_date)
              select to_char(start_date + n, 'mon yyyy') "date", count(0) "count" from pr2
              group by to_char(start_date + n, 'mon yyyy')
              order by 1