3 Replies Latest reply on Feb 9, 2013 12:43 PM by ---Brodyaga---

# Count Number of Days in Between Two Dates in Correct Month

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
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(*)
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
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
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``````