Query Help Running Total - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query Help Running Total

Author

Topic

mvanwyk
Yak Posting Veteran

99 Posts

Posted - 2009-07-22 : 06:03:55

Hi Guys.

Please bare with me as i explain an issue i have.

I Have a table called PCH1 (SAP B1).
It has several columns, so I’m only going to mention a few.

Description TotalSumSy Project ActDelDate
something 1 8700 mvw001 01/07/2008
something 2 5667 mvw001 01/07/2008
something 3 4556 mvw001 01/08/2008
something 4 8000 mvw001 01/08/2008
something 5 700 mvw001 01/08/2008
something 6 500 mvw001 01/08/2008
something 7 499 mvw001 01/09/2008
something 8 4556 mvw001 01/10/2008
something 9 8000 mvw001 01/10/2008
something 10 700 mvw001 01/11/2008
something 11 500 mvw001 01/12/2008
something 12 499 mvw001 01/01/2009
something 13 4556 mvw001 01/02/2009
something 14 8000 mvw001 01/03/2009

What I have so far looks like this.

SELECT o1.Project,
MONTH(o1.ActDelDate) AS BudgetMonth,
YEAR(o1.ActDelDate) AS BudgetYear,
o1.TotalSumSy, SUM(o2.TotalSumSy)AS RunningTotalAE

FROM SBO_Voortrekkers.dbo.PCH1 AS o1 INNER JOIN
SBO_Voortrekkers.dbo.PCH1 AS o2 ON YEAR(o1.ActDelDate) >= YEAR(o2.ActDelDate) AND MONTH(o1.ActDelDate) >= MONTH(o2.ActDelDate) AND
o1.Project = o2.Project
WHERE (o1.Project <> '')
GROUP BY o1.Project, YEAR(o1.ActDelDate), MONTH(o1.ActDelDate), o1.TotalSumSy

But The running Total (RunningTotalAE) resets to 0 after each year, what am I missing here?

Any help would be appreciated.

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-07-22 : 07:25:24

If you don't want to reset 0, ON condition should look like this
ON o1.ActDelDate >= o2.ActDelDate AND o1.Project = o2.Project

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

mvanwyk
Yak Posting Veteran

99 Posts

Posted - 2009-07-23 : 11:46:21

Hi Please note that does not work, can you think why?
Please see section marked with ### if i understand this correctly a Running Total should never be less than the previous Total

Project M Y TotalSumSy
KAAP7 3 2007 344.3 344.3
KAAP7 6 2007 27556.8 27901.1
KAAP7 7 2007 595.4 59822.18
KAAP7 7 2007 2884.2 59822.18
KAAP7 7 2007 3141.48 59822.18
KAAP7 7 2007 25300 53201.1############
KAAP7 7 2007 64403.4 124225.58
KAAP7 8 2007 720 427677.33
KAAP7 8 2007 810.75 142559.11
KAAP7 8 2007 820.8 142559.11
KAAP7 8 2007 7200 131425.58
KAAP7 8 2007 7341.98 142559.11
KAAP7 10 2007 1348 143907.11
KAAP7 10 2007 86596.65 230503.76
KAAP7 11 2007 92782.13 323285.89
KAAP7 2 2008 1429.12 327952.01
KAAP7 2 2008 3237 326522.89
KAAP7 3 2008 720 336978.74
KAAP7 3 2008 727.78 336978.74
KAAP7 3 2008 7578.95 335530.96
KAAP7 4 2008 864 364721.1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources