# Thread: Cumulative Sum for Oct-Oct YTD - Query Help!

1. Registered User
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. Registered User
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. Registered User
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. Registered User
Join Date
Oct 2008
Posts
19
Below worked very well - Thanks!

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. Registered User
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. Registered User
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
•