Oracle SQL query count of open projects grouped by month

Question

I am given a view to project data and was asked to summarize how many projects are/were open in a given month.

PROJECT_ID          OPEN_DATE         CLOSE_DATE         OWNER
         1          8/01/2012         09/01/2012          JEFF     
         2          8/08/2012         10/01/2012          JEFF     
         3          9/01/2012               Null          JEFF     
         4          9/12/2012               Null          JEFF     
         5          9/24/2012         11/01/2012          JEFF     
         6         10/01/2012         12/01/2012          JEFF     
         7         10/09/2012         01/01/2013          JEFF     

I need OPEN counts such as this. Once I get the basic query down, I'll need to filter/group by project owner

AUG12   SEPT12   OCT12   NOV12   DEC12   JAN13   FEB13
2        4       5       4       3       2       1
^        ^       ^       ^       ^       ^       ^
|        |       |       |       |       |       |-> From Projects 3 & 4
|        |       |       |       |       |-> From Projects 3 & 4 
|        |       |       |       |-> From Projects 3,4 & 7
|        |       |       |-> From Projects 3,4,6 & 7
|        |       |-> From Projects 3,4,5,6 & 7
|        |-> From Projects 2,3,4, & 5                                    
|-> From Project 1 & 2

Latest This is what I have based on the connect by suggestion by Gordon. The query is slow about 4-5 seconds

SELECT SUM(case when (OPEN_DATE <= thedate and CLOSE_DATE > thedate) or (OPEN_DATE <= thedate and CLOSE_DATE Is Null) then 1 else 0 end) as Open 
1.     From (select * FROM Project 
2.     WHERE Project.Owner = :owner AND Project.action_for = :actionFor )  
3.     cross join (   select add_months(last_day(SYSDATE), level-7)

4.        as thedate from  dual connect by level <= 12   ) 
5.      group by to_char(thedate, 'YYYY-MM') order by 1

Besides being slow, I'm not sure it is working correctly with my real data. I need to decompose the query to give me results that I can manually check the results. How does one debug a sql statement? I mean just because you get results how do you know they are correct?

Answer 1

This will return the count of projects that weren't closed in the month that they were opened. This sounds like what you were asking for

   SELECT to_char(open_date, 'MON-YYYY') as OpenMonths, count(*) as counts FROM 
  Project WHERE to_char(open_date,'MON-YYYY') <> to_char(close_date,'MON-YYYY') 
    GROUP BY to_char(open_date, 'MON-YYYY');

The link to the sqlfiddle is here. http://www.sqlfiddle.com/#!4/28bf5/1 I changed the nulls to other example dates because the text to DDL wasn't detecting them correctly.

Edit:

Using a generated month/year dimension, this query will work

   SELECT monthid, count(*) as ProjectsOpen FROM
  (
   Select * FROm table1 CROSS JOIN mdim WHERE 
  (to_date(mdim.monthid,'MON-YYYY') >= table1.open_date ) AND (to_date(mdim.monthid,'MON-YYYY') <= table1.close_date)
  ) GROUP BY monthid;

Where the month dimension is something like

     INSERT ALL 
 INTO mdim 
     VALUES ('JAN-2012')
INTO mdim 
     VALUES ('FEB-2012')
INTO mdim 
     VALUES ('MAR-2012')
INTO mdim 
     VALUES ('APR-2012')
INTO mdim 
     VALUES ('MAY-2012')
INTO mdim 
     VALUES ('JUN-2012')
INTO mdim 
     VALUES ('JUL-2012')
INTO mdim 
     VALUES ('AUG-2012')
INTO mdim 
     VALUES ('SEP-2012')
INTO mdim 
     VALUES ('OCT-2012')
INTO mdim 
     VALUES ('NOV-2012')
INTO mdim 
     VALUES ('DEC-2012')
INTO mdim 
     VALUES ('JAN-2013')
INTO mdim 
     VALUES ('FEB-2013')
INTO mdim 
     VALUES ('MAR-2013')
INTO mdim 
     VALUES ('APR-2013')
INTO mdim 
     VALUES ('MAY-2013')
INTO mdim 
     VALUES ('JUN-2013')
INTO mdim 
     VALUES ('JUL-2013')
INTO mdim 
     VALUES ('AUG-2013')
INTO mdim 
     VALUES ('SEP-2013')
INTO mdim 
     VALUES ('OCT-2013')
INTO mdim 
     VALUES ('NOV-2013')
INTO mdim 
     VALUES ('DEC-2013') 

Since the SQLFiddle screwed up the csv import, I'm doing some arbitrary to_date casting. But this second query will get the data you want if you populate your month dimension.

fiddle : http://www.sqlfiddle.com/#!4/ca8ac/34

Answer 2

Here is one way. It puts the values on separate rows, rather than in separate columns. I find that easier to work with anyway:

select owner, to_char(thedate, 'YYYY-MM') as YYYYMM,
       SUM(case when open_date <= thedate and close_date > thedate then 1 else 0 end) as cnt
from project p cross join
     (select to_date('2012-08-31', 'yyyy-mm-dd') as thedate from dual union all
      select to_date('2012-09-30', 'yyyy-mm-dd') as thedate from dual union all
      select to_date('2012-10-31', 'yyyy-mm-dd') as thedate from dual union all
      select to_date('2012-11-30', 'yyyy-mm-dd') as thedate from dual union all
      select to_date('2012-12-31', 'yyyy-mm-dd') as thedate from dual union all
      select to_date('2013-01-31', 'yyyy-mm-dd') as thedate from dual union all
      select to_date('2013-02-28', 'yyyy-mm-dd') as thedate from dual
     ) as monthends
group by owner, to_char(thedate, 'YYYY-MM')
order by 1

If you want to create the monthends table for an arbitrary range, you could learn about Oracle's connect by syntax. An easier way to get a bunch of months is simply:

select add_month('01Jan2000', rownum) - 1
from project
where rownum < 12 * 20

That will get the month end date for 2000-2019.