1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query help (COUNT records for each month over last year)

Discussion in 'SQL PL/SQL' started by madlan, Nov 10, 2010.

  1. madlan

    madlan Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    I'm trying to work out the monthly totals (of records) by two date fields.
    These are OPENED and CLOSED

    I want a total number of records that have the above values fall into each month for the past 13 months (rolling).

    So far, I have:

    Code (SQL):

    SELECT
    TO_CHAR(i.OPENED, 'MONTH') AS MONTH,
    COUNT(i.INC_ID) AS Opened
    FROM incident i
    WHERE AND TO_DATE(i.OPENED, 'YYYY-MM-DD')  < TO_DATE(SYSDATE, 'YYYY-MM-DD')
    AND TO_DATE(i.OPENED, 'YYYY-MM-DD') >= TO_DATE(add_months (to_date(SYSDATE), -13 )+1, 'YYYY-MM-DD')
    GROUP BY i.OPENED
     
    This is returning dates outside of the rolling year defined above (back to 1999)
    Am I doing something wrong with the date functions?
    This only does the OPENED column at the moment.(I guess a CASE would be best similar to MSSQL)
     
  2. madlan

    madlan Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    It must be my use of the date functions, using static dates works fine:

    Code (SQL):


    SELECT
    TO_CHAR(i.OPENED, 'MONTH') AS MONTH,
    SUM(CASE WHEN i.DATE_OPENED >= TO_DATE('2010-01-01', 'YYYY-MM-DD') AND i.OPENED < TO_DATE('2010-12-31', 'YYYY-MM-DD') THEN 1 ELSE 0 END) AS Opened,
    SUM(CASE WHEN i.CLOSED >= TO_DATE('2010-01-01', 'YYYY-MM-DD') AND i.CLOSED < TO_DATE('2010-12-31', 'YYYY-MM-DD') THEN 1 ELSE 0 END) AS Closed
    FROM incident i
    GROUP BY TO_CHAR(i.OPENED, 'MONTH')

     
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Why are you using TO_DATE() on DATE columns? It is completely unnecessary and can cause the problems you experience. Your where clause should look similar to this:

    Code (SQL):
     
    WHERE OPENED < SYSDATE
    AND OPENED >= add_months (SYSDATE, -13 )+1

     
     
  4. madlan

    madlan Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    The below is working great, except for two issues I cannot resolve, How do I order the months correctly (Ordering the column will cause them to display alphabetically) and if I group by month, the 13th month will be grouped with the first?


    Code (SQL):

    SELECT
        TO_CHAR(i.OPENED, 'MONTH') AS MONTH,
        SUM(CASE WHEN i.OPENED >= add_months (SYSDATE, -13 )+1 AND i.OPENED < SYSDATE THEN 1 ELSE 0 END) AS Opened,
        SUM(CASE WHEN i.CLOSED >= add_months (SYSDATE, -13 )+1 AND i.CLOSED < SYSDATE THEN 1 ELSE 0 END) AS Opened
    FROM incident i
     
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):
     
    SQL> COLUMN YEAR noprint
    SQL> SELECT
      2          TO_CHAR(i.OPENED, 'MONTH') AS MONTH,
      3          TO_CHAR(i.OPENED, 'RRRR') AS YEAR,
      4          SUM(CASE WHEN i.OPENED >= add_months (SYSDATE, -13 )+1 AND i.OPENED < SYSDATE THEN 1 ELSE 0 END) AS Opened,
      5          SUM(CASE WHEN i.CLOSED >= add_months (SYSDATE, -13 )+1 AND i.CLOSED < SYSDATE THEN 1 ELSE 0 END) AS Closed
      6  FROM incident i
      7  GROUP BY to_char(i.opened, 'MONTH'), to_char(i.opened, 'RRRR')
      8  /
     
    MONTH                           OPENED     CLOSED
    --------------------------- ---------- ----------
    NOVEMBER                            50         50
    NOVEMBER                            50         50
     
    SQL>

     
     
  6. madlan

    madlan Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi David, I cannot get only the rolling 13 months to display, I get rows for all months and years recorded:

    Code (Text):

    JANUARY     1997    0   0
    JULY        1997    0   0
    JANUARY     1998    0   0
    MAY         1999    0   0
    APRIL       2001    0   0
    OCTOBER     2001    0   0
    AUGUST      2002    0   0
    NOVEMBER    2002    0   0
    JULY        2002    0   0
    JANUARY     2003    0   0
    JUNE        2003    0   0
    OCTOBER     2004    0   0
    SEPTEMBER   2004    0   0
    MARCH       2005    0   0
    APRIL       2005    0   0
    NOVEMBER    2006    0   0
    JANUARY     2009    0   0
    AUGUST      2010    54  43
     
    How do I order while still returning the month? Not a problem without the textual value:

    Code (SQL):

    ORDER BY  TO_CHAR(i.OPENED, 'RRRR'), TO_CHAR(i.OPENED, 'MM')
     
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Try this:

    Code (SQL):
     
    ORDER BY  to_number(TO_CHAR(i.OPENED, 'RRRR')), TO_CHAR(i.OPENED, 'MM')
     
    Ordering by character values won't put the years in numeric order.