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

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 ;

If you can use OLAP functions, this will work.

Below worked very well - Thanks!

> 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'"

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.