Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2008
    Posts
    19

    Unanswered: Cumulative Sum for Oct-Oct YTD - Query Help!

    I need to calculate a rolling (cumulative) sum for the dates shown (as the financial year is Oct - Oct).
    I have a query shown below:
    SELECT
    MONTH(DDATE) AS MN,
    SUBSTR(CHAR(DDATE,ISO),1,7) AS DATE_FIELD,
    SUM(VOLUME) AS ACTUAL
    FROM FACT
    WHERE DDATE BETWEEN '10-01-2007' AND '09-30-2008'
    GROUP BY
    MONTH(DDATE) AS MN,
    SUBSTR(CHAR(DDATE,ISO),1,7)
    ORDER BY
    SUBSTR(CHAR(DDATE,ISO),1,7)

    and this returns the month by month actuals nicely such as:
    MN DATE_FIELD ACTUAL
    10 2007-10 7
    11 2007-11 9
    12 2007-12 4
    01 2008-01 5

    What I would like is an extra column showing the monthly rolling sum of the ACTUAL values.

    I've tried doing a nested join such as (in pseudo code):

    SELECT A.MN,A.DATE_FIELD,A.ACTUAL,SUM(B.ACTUAL) AS RUNNING_SUM
    FROM
    ( --SELECT STATEMENT IDENTICAL TO ABOVE) A
    INNER JOIN
    ( --EXACTLY THE SAME SELECT STATEMENT AGAIN) B
    ON
    A.MN >= B.MN
    GROUP BY ...

    I'm not sure what I should be joining on and whether just using >= is sufficient. Any help would be appreciated.

    Thanks

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    This query might work:


    WITH abcde ( DATE_FIELD , ACTUAL ) AS
    (
    SUBSTR(CHAR(DDATE,ISO),1,7) AS DATE_FIELD,
    SUM(VOLUME) AS ACTUAL
    FROM FACT
    WHERE DDATE BETWEEN '10-01-2007' AND '09-30-2008'
    GROUP BY
    SUBSTR(CHAR(DDATE,ISO),1,7)
    )
    SELECT A.DATE_FIELD, A.ACTUAL, SUM(B.ACTUAL)
    FROM abcde A INNER JOIN abcde B
    ON A.DATE_FIELD >= B.DATE_FIELD
    GROUP BY A.DATE_FIELD , A.ACTUAL ;

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you can use OLAP functions, this will work.
    Code:
    SELECT
           MONTH(DDATE) AS MN,
           SUBSTR(CHAR(DDATE,ISO),1,7) AS DATE_FIELD,
           SUM(VOLUME) AS ACTUAL
         , SUM(SUM(VOLUME)) OVER(ORDER BY SUBSTR(CHAR(DDATE,ISO),1,7)
                                 ROWS BETWEEN UNBOUNDED PRECEDING
                                          AND CURRENT ROW) AS running_sum
      FROM FACT
     WHERE DDATE BETWEEN '2007-10-01' AND '2008-09-30'
     GROUP BY
           MONTH(DDATE),
           SUBSTR(CHAR(DDATE,ISO),1,7)
     ORDER BY 
           DATE_FIELD
    ;

  4. #4
    Join Date
    Oct 2008
    Posts
    19
    Below worked very well - Thanks!


    Quote Originally Posted by umayer
    This query might work:


    WITH abcde ( DATE_FIELD , ACTUAL ) AS
    (
    SUBSTR(CHAR(DDATE,ISO),1,7) AS DATE_FIELD,
    SUM(VOLUME) AS ACTUAL
    FROM FACT
    WHERE DDATE BETWEEN '10-01-2007' AND '09-30-2008'
    GROUP BY
    SUBSTR(CHAR(DDATE,ISO),1,7)
    )
    SELECT A.DATE_FIELD, A.ACTUAL, SUM(B.ACTUAL)
    FROM abcde A INNER JOIN abcde B
    ON A.DATE_FIELD >= B.DATE_FIELD
    GROUP BY A.DATE_FIELD , A.ACTUAL ;

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    > Below worked very well - Thanks!
    Really?
    I thought that "WHERE DDATE BETWEEN '10-01-2007' AND '09-30-2008'"
    should be
    "WHERE DDATE BETWEEN '2007-10-01' AND '2008-09-30'"
    or
    "WHERE DDATE BETWEEN '10/01/2007' AND '09/30/2008'"

  6. #6
    Join Date
    Oct 2008
    Posts
    19
    It did work as MM-DD-YYYY but only because I've been using Toad for DB2 Freeware and for some reason, in this software it is expecting the dates as MM-DD-YYYY. I actually do have the date as DATE(YYYY-MM-DD) when I'm writing the finished queries.

Posting Permissions

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