Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2012
    Posts
    2

    Unanswered: Query and Counts between date ranges

    Using Oracle 10. I have a table that contains the start and end dates of a process by its status. I am trying to run a query that will return a count of each process by status for each month-year the process is running in. Where I am struggling is counting each record for every month-year between it’s start and end date. So, if a process starts 09-2011 and ends 01-2012 it counts for 1 in 09-2011, 10-2011,11-2011,12-2011 and 01-2012. I am trying to do this within a select but if needed I can do it as a procedure.

    So for output I am trying to get:

    Month/Year Count Status
    MM-YYYY # xxxxxx

    Output I am trying to get based on attached insert statements:
    05-2012 1 open
    06-2012 2 open
    07-2012 2 open
    08-2012 2 open
    08-2012 1 closed
    09-2012 3 closed
    10-2012 2 closed
    11-2012 1 closed
    08-2012 1 submitted
    09-2012 1 submitted
    10-2012 1 submitted

    DDL is attached.

    Any and all thoughts are greatly appreciated.

    Thank you!
    Attached Files Attached Files

  2. #2
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Code:
    dayneo@RMSD> alter session set nls_date_format="mm/dd/yyyy";
    
    Session altered.
    
    dayneo@RMSD> 
    dayneo@RMSD> CREATE TABLE START_END
      2  (
      3    END_DATE_REQ	    DATE,
      4    START_DATE_REQ	    DATE,
      5    STATUS_ID	    VARCHAR2(30 BYTE)
      6  );
    
    Table created.
    
    dayneo@RMSD> begin
      2    Insert into START_END(START_DATE_REQ, END_DATE_REQ, STATUS_ID)
      3  		      Values('08/15/2012', '08/23/2012', 'submitted');
      4    Insert into START_END(START_DATE_REQ, END_DATE_REQ, STATUS_ID)
      5  		      Values('09/05/2012', '10/13/2012', 'closed');
      6    Insert into START_END(START_DATE_REQ, END_DATE_REQ, STATUS_ID)
      7  		      Values('09/25/2012', '10/23/2012', 'submitted');
      8    Insert into START_END(START_DATE_REQ, END_DATE_REQ, STATUS_ID)
      9  		      Values('06/06/2012', '08/13/2012', 'open');
     10    Insert into START_END(START_DATE_REQ, END_DATE_REQ, STATUS_ID)
     11  		      Values('09/15/2012', '11/18/2012', 'closed');
     12    Insert into START_END(START_DATE_REQ, END_DATE_REQ, STATUS_ID)
     13  		      Values('05/15/2012', '08/17/2012', 'open');
     14    Insert into START_END(START_DATE_REQ, END_DATE_REQ, STATUS_ID)
     15  		      Values('08/15/2012', '09/30/2012', 'closed');
     16    Insert into START_END(START_DATE_REQ, END_DATE_REQ, STATUS_ID)
     17  		      Values('06/24/2012', '08/24/2012', 'submitted');
     18    commit;
     19  end;
     20  /
    
    PL/SQL procedure successfully completed.
    
    dayneo@RMSD> create table table_of_months(mnth date)
      2  /
    
    Table created.
    
    dayneo@RMSD> declare
      2  	     l_mnth date;
      3  	     l_max  date;
      4  begin
      5  
      6  	     select trunc(min(start_date_req), 'month'), trunc(max(end_date_req), 'month')
      7  	  into l_mnth, l_max
      8  	  from start_end;
      9  
     10  	     while l_mnth <= l_max loop
     11  
     12  		     insert into table_of_months values(l_mnth);
     13  		     l_mnth := add_months(l_mnth, 1);
     14  
     15  	     end loop;
     16  
     17  end;
     18  /
    
    PL/SQL procedure successfully completed.
    
    dayneo@RMSD> select * from table_of_months
      2  /
    
    MNTH
    ----------
    05/01/2012
    06/01/2012
    07/01/2012
    08/01/2012
    09/01/2012
    10/01/2012
    11/01/2012
    
    7 rows selected.
    
    dayneo@RMSD> select m.mnth, count(*), t.status_id
      2    from start_end t,
      3  	    table_of_months m
      4   where m.mnth between trunc(t.start_date_req, 'month')
      5  		       and trunc(t.end_date_req, 'month')
      6  group by m.mnth, t.status_id
      7  order by t.status_id, m.mnth
      8  /
    
    MNTH         COUNT(*) STATUS_ID
    ---------- ---------- ------------------------------
    08/01/2012          1 closed
    09/01/2012          3 closed
    10/01/2012          2 closed
    11/01/2012          1 closed
    05/01/2012          1 open
    06/01/2012          2 open
    07/01/2012          2 open
    08/01/2012          2 open
    06/01/2012          1 submitted
    07/01/2012          1 submitted
    08/01/2012          2 submitted
    09/01/2012          1 submitted
    10/01/2012          1 submitted
    
    13 rows selected.
    
    dayneo@RMSD>

  3. #3
    Join Date
    Aug 2012
    Posts
    2

    Thumbs up Many Thanks!

    Thank you dayneo! That did the trick.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •