running total in table variable

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 total in table variable

Author

Topic

chris_wood99
Yak Posting Veteran

70 Posts

Posted - 2012-05-18 : 11:33:13

Hi All,

I am just testing the performance of some different sub queries vs table variables, etc for small sized tables, I have the below query, but I want the @runQTY variable to be reset for every customer?

how can I keep this as a table variable and reset the running qty for each customer ?

DECLARE @SalesTbl TABLE (custid smallint, ordermonth datetime, qty int, runqty int)
DECLARE @runQTY int = 0

INSERT INTO @SalesTbl
SELECT o.custid, so.ordermonth, so.qty, null
FROM sales.custOrders so
INNER JOIN Sales.Orders o
ON so.custid = o.custid
GROUP BY o.custid, so.ordermonth, so.qty

UPdate @SalesTbl
Set @runqty = Runqty = @Runqty + qty
Select * from @SalesTbl

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2012-05-18 : 13:06:33

Should RunQTY be equal to the number of rows in @SalesTbl for each respective customer?

chris_wood99
Yak Posting Veteran

70 Posts

Posted - 2012-05-18 : 13:38:18

Yes a cumulative total that gets reset when it moves onto the next custid

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-05-18 : 14:18:39

You can do this - but not sure this technique (quirky update as some call it) will guarantee the order of the updates unless this table is a permanent table with clustered index.

EDIT:
Here are a couple discussions about it with another poster(Jeff Moden). Also, I think he wrote an article about it on sqlservercentral.com
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93431
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93091&whichpage=2


DECLARE @SalesTbl TABLE (custid smallint, ordermonth datetime, qty int, runqty int)

DECLARE @runQTY int 
declare @prevCustid int

INSERT INTO @SalesTbl

select 1, 1,10, null union all
select 1, 1,12, null union all
select 2, 1,13, null union all
select 2, 2,10, null union all
select 3, 2,12, null union all
select 3, 2,13, null union all
select 3, 2,10, null union all
select 4, 2,12, null union all
select 4, 2,13, null 


UPdate @SalesTbl set 
       @runqty = Runqty = case when custid = @prevCustid then @Runqty + qty else qty end
       ,@prevCustid = custid
       
Select * from @SalesTbl

OUTPUT:
custid ordermonth              qty         runqty
------ ----------------------- ----------- -----------
1      1900-01-02 00:00:00.000 10          10
1      1900-01-02 00:00:00.000 12          22
2      1900-01-02 00:00:00.000 13          13
2      1900-01-03 00:00:00.000 10          23
3      1900-01-03 00:00:00.000 12          12
3      1900-01-03 00:00:00.000 13          25
3      1900-01-03 00:00:00.000 10          35
4      1900-01-03 00:00:00.000 12          12
4      1900-01-03 00:00:00.000 13          25

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-19 : 15:44:37

actually if you're on sql 2005 or higher you can achieve cumulative sum using other methods also like apply

see scenario 1

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources