Running Totals Question - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Running Totals Question

Author

Topic

TheTruth
Starting Member

3 Posts

Posted - 2014-11-16 : 21:04:07

Hello Friends,

I'm a beginner in SQL, trying to automate a process currently being performed in Excel. I'd like to create a column that contains a running total that resets whenever the Item ID changes.

Here is a simplified example:
[url]http://imgur.com/LEiZGUM[/url]
Column F is what I'm struggling with

Assume that all but the last two columns are already in a single temporary table.

I'm using SSMS 2008.

New to the forum, so please let me know if I left out important info or posted in the wrong section.

Thank you for your help!

theboyholty
Posting Yak Master

226 Posts

Posted - 2014-11-17 : 10:31:57

Hi.

I am assuming you've got your data in a SQL table and its called tmp (it wouldn't be called that but you'll need to change it to whatever it is). When you build your table, you'll need to add an ID field, some kind of unique identifier that is an integer.

Here's the code I quickly used to knock up a test table:


create table tmp (RowID int identity(1,1),ItemID int,QtyReceived int

Then you create a function to accept the ItemID and the RowID and to output the total for your QtyReceived column:


create function udf_RunningTotal (@ItemID int, @RowID int)
RETURNS int
AS
BEGIN
	declare @retval int
	set @retval = (select SUM(QtyReceived) as sum_QtyReceived
	from tmp
	where ItemID = @ItemID
	and	RowID <= @RowID)
	RETURN @retval
END
GO

Finally you use a CROSS APPLY to match your original table to the result of the function:


select *
from tmp t1
cross apply (
	select dbo.udf_RunningTotal(ItemID,RowID) as QtyReceived
	) d

Hopefully that will give you your answer. It works for me using dummy data. Its tricky doing running totals because you're effectively joining rows from the same table to each other.

Make sure you read through and try to understand the code. If you're a beginner, then this is fairly advanced stuff using cross apply and functions etc.

Good luck.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-17 : 11:24:00

Worth noting that you can do this with a window function in Sql Server 2012.

TheTruth
Starting Member

3 Posts

Posted - 2014-11-18 : 14:38:27

Thank you. I tested the CROSS APPLY method and it worked. I spent a lot of time reading, and I still don't understand exactly how it works so I'd rather go a different way if possible.

In my searching, I think I may be able to do this using the OVER clause. I attempted using this in the aggregate line below:
___________________________________
SELECT #tmp.Region,
#tmp.Location,
#tmp.ItemID,
#tmp.DateReceived,
#tmp.QtyReceived,
#tmp.CurrentInventory,
SUM(#tmp.QtyReceived) OVER(PARTITION BY #tmp.Region, #tmp.Location, #tmp.ItemID ORDER BY #tmp.DateReceived DESC) AS "RunningTotal"

FROM #tmp;
___________________________________
Msg102, Level 15, State 1, Line64
Incorrect syntax near 'order'.
___________________________________

My goal is to have the running total reset whenever it encounters a different item ID, location, region and to sequence the running total by DateReceived descending. (the report is being used to determine the age of the inventory).

Sorry for the bother after an already excellent suggestion.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-18 : 15:05:04

ORDER BY doesn't apply to the SUM window function in SQL Server 2008. You need at least 2012 for that.

TheTruth
Starting Member

3 Posts

Posted - 2014-11-18 : 15:38:47

quote:
Originally posted by gbritton

ORDER BY doesn't apply to the SUM window function in SQL Server 2008. You need at least 2012 for that.

You're right. I took out the "ORDER BY" and it ran. Doesn't do what I was hoping it would, but it ran. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources