Running Totals with Parameters

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

SQL Server 2000 Forums

SQL Server Development (2000)

Running Totals with Parameters

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-11-04 : 07:13:07

Chris writes "Thanks for the article on running totals, it's put me on the right track. However, how do you go about calculating a running total for a parameter range. For example, the running total for this:

SELECT a.DayCount,
a.Sales,
SUM(b.Sales) AS RunningTotal, a.other
FROM Sales a
CROSS JOIN Sales b
WHERE (b.DayCount <= a.DayCount)
GROUP BY a.DayCount,a.Sales, a.other
ORDER BY a.DayCount,a.Sales


DayCount Sales                 RunningTotal          
-------- --------------------- --------------------- 
1        120.0000              120.0000
2        60.0000               180.0000
3        125.0000              305.0000
4        40.0000               345.0000
5        10.0000               355.0000
6        25.0000               380.0000
7        40.0000               420.0000
8        55.0000               475.0000
9        70.0000               545.0000
10       85.0000               630.0000
11       100.0000              730.0000
12       115.0000              845.0000
13       130.0000              975.0000
14       145.0000              1120.0000

retrieves the running total (cumulative) for all records. What if I only wanted the records from DayCount 6 to DayCount 12? E.g.



DayCount Sales                 RunningTotal          
-------- --------------------- --------------------- 

6        25.0000               380.0000
7        40.0000               420.0000
8        55.0000               475.0000
9        70.0000               545.0000
10       85.0000               630.0000
11       100.0000              730.0000
12       115.0000              845.0000

returns the running total for all records but restricts it to the range specified. How do I calculate the running total starting from the first in the range specified?"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-11-04 : 07:44:28

surely that's a display issue....
however why not wrap the first code with something like.....

select * from (
SELECT a.DayCount,
a.Sales,
SUM(b.Sales) AS RunningTotal, a.other
FROM Sales a
CROSS JOIN Sales b
WHERE (b.DayCount <= a.DayCount)
GROUP BY a.DayCount,a.Sales, a.other
ORDER BY a.DayCount,a.Sales) c
where c.daycount > 5
order by c.daycount

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-04 : 07:47:12

[code]WHERE (b.DayCount <= a.DayCount)
AND a.DayCount between 6 and 12
AND b.DayCount between 6 and 12
[/code]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-04 : 08:04:50

And, as always: if you are doing this just to display as a column in a report, I HIGHLY recommend calculating it at the presentation layer on the report itself instead of in SQL. The only time you should be doing running totals in SQL is if further calculations with SQL depend on those running totals. (i.e., calculating taxes up to a YTD ceiling or something like that)

It is much easier and more efficent to have a reporting tools like Crystal or even ASP calculate running totals rather than to force SQL Server to.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources