Calculate a Running Total in SQL Server

0 votes

Imagine the following table (called TestTable):

id     somedate    somevalue
--     --------    ---------
45     01/Jan/09   3
23     08/Jan/09   5
12     02/Feb/09   0
77     14/Feb/09   7
39     20/Feb/09   34
33     02/Mar/09   6

I would like a query that returns a running total in date order, like:

id     somedate    somevalue  runningtotal
--     --------    ---------  ------------
45     01/Jan/09   3          3
23     08/Jan/09   5          8
12     02/Feb/09   0          8
77     14/Feb/09   7          15  
39     20/Feb/09   34         49
33     02/Mar/09   6          55

I know there are various ways of doing this in SQL Server 2000 / 2005 / 2008.

I am particularly interested in this sort of method that uses the aggregating-set-statement trick:

INSERT INTO @AnotherTbl(id, somedate, somevalue, runningtotal) 
   SELECT id, somedate, somevalue, null
   FROM TestTable
   ORDER BY somedate

DECLARE @RunningTotal int
SET @RunningTotal = 0

UPDATE @AnotherTbl
SET @RunningTotal = runningtotal = @RunningTotal + somevalue
FROM @AnotherTbl

Although I have heard there are problems with this because you can't always be sure that the UPDATE statement will process the data in the right sequence, it is still incredibly efficient. We might be able to find some conclusive solutions to that problem.

But perhaps others have ideas for different approaches. Can someone please help me with this?

Sep 9 in Database by Kithuzzz
• 11,640 points
21 views

No answer to this question. Be the first to respond.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.

Related Questions In Database

0 votes
0 answers
0 votes
0 answers

Calculate time difference in minutes in SQL Server

I require the minutes-based time difference between ...READ MORE

Aug 13 in Database by Kithuzzz
• 11,640 points
107 views
0 votes
0 answers

Get week day name from a given month, day and year individually in SQL Server

I'm attempting to get a day name ...READ MORE

Aug 13 in Database by Kithuzzz
• 11,640 points
59 views
0 votes
0 answers

What represents a double in sql server?

I have several properties in C# which ...READ MORE

Aug 13 in Database by Kithuzzz
• 11,640 points
25 views
0 votes
0 answers

Import .bak file to a database in SQL server

I have a file with .bak extension. How ...READ MORE

Aug 14 in Database by Kithuzzz
• 11,640 points
13 views
0 votes
0 answers

How to Create a real one-to-one relationship in SQL Server?

I have two tables, Country and Capital, ...READ MORE

Aug 18 in Database by Kithuzzz
• 11,640 points
21 views
0 votes
1 answer

How do I UPDATE from a SELECT in SQL Server?

MERGE INTO YourTable T USING ...READ MORE

answered Feb 3 in Database by Vaani
• 7,020 points
119 views
0 votes
0 answers

How do I UPDATE from a SELECT in SQL Server?

INSERT INTO Table (col1, col2, col3) SELECT col1, ...READ MORE

Feb 4 in Database by Vaani
• 7,020 points
44 views
0 votes
1 answer

What is a stored procedure?

A stored procedure is a set of ...READ MORE

answered Feb 4 in Database by Neha
• 8,920 points
214 views
0 votes
1 answer

LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

At the top level there are mainly ...READ MORE

answered Feb 4 in Database by Neha
• 8,920 points
145 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP