This content has been marked as final. Show 3 replies
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
Welcome to the forum!
Here's one way:
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.
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 ;
Edited by: Frank Kulash on Feb 8, 2013 4:42 PM
Justin's solution is simpler and better.
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