running total - stumped - 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 2005 Forums

Transact-SQL (2005)

running total - stumped

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2008-09-11 : 16:13:30

I am trying to get the running total of my field 'trans.TransactionQty'. I have the following code, but my SUM OVER gives me the grand total on each row; I need the running total. I have looked at the forums and have seen a couple close, but I can't figure it out.

output should be:

ItemNumber TotalQtyOnHand StdCost TransactionDate TransactionQty UnitCost OrderOfTransaction RunningTotalQtyOnHand

SELECT

mstr.ItemNumber,mstr.TotalQtyOnHand, mstr.StdCost,
trans.TransactionDate,trans.TransactionQty, trans.UnitCost,
ROW_NUMBER() OVER(PARTITION BY mstr.ItemNumber ORDER BY mstr.ItemNumber,trans.TransactionDate desc)AS TranOrder,
SUM(trans.TransactionQty) OVER(PARTITION BY mstr.ItemNumber )AS TranQuant

FROM
MAS_SPN_IM1_InventoryMasterfile AS mstr INNER JOIN
MAS_SPN_IM5_TransactionDetail AS trans ON mstr.ItemNumber = trans.ItemNumber AND mstr.StdCost <> trans.UnitCost

WHERE (trans.TransactionCode = 'PO') AND SUBSTRING(mstr.ItemDescription,1,5)<>'Merge' AND mstr.TotalQtyOnHand>0 AND mstr.ItemNumber='1003305-00'
ORDER BY mstr.ItemNumber, trans.TransactionDate DESC

Any help would be greatly appreciated. Thank you.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-09-11 : 16:48:15

You can obviously combine the two queries, but it was easier to see what was going on this way.


SELECT 
mstr.ItemNumber
,mstr.TotalQtyOnHand
,mstr.StdCost
,trans.TransactionDate
,trans.TransactionQty
,trans.UnitCost
,ROW_NUMBER() OVER(PARTITION BY mstr.ItemNumber ORDER BY mstr.ItemNumber,trans.TransactionDate desc)AS TranOrder
into #Tmp
FROM 
MAS_SPN_IM1_InventoryMasterfile AS mstr INNER JOIN
MAS_SPN_IM5_TransactionDetail AS trans ON mstr.ItemNumber = trans.ItemNumber AND mstr.StdCost <> trans.UnitCost
WHERE (trans.TransactionCode = 'PO') AND SUBSTRING(mstr.ItemDescription,1,5)<>'Merge' AND mstr.TotalQtyOnHand>0 AND mstr.ItemNumber='1003305-00'


Select *
,(Select SUm(aa.TransactionQty) from #Tmp aa  where aa.TranOrder <= a.TranOrder and aa.ItemNumber = a.ItemNumber) as TranQuant
from #Tmp a
ORDER BY ItemNumber, TransactionDate DESC

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

osupratt
Posting Yak Master

238 Posts

Posted - 2008-09-12 : 09:27:13

Thank you for the reply. It works, but I still don't understand fully what is taking place for it to work. Could you explain please why a temp table is needed (if it's needed) and why you did it this way? Thanks again for the response.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-09-12 : 11:03:31

A #tmp table is not needed, but it was easier to illustrate what was being done (It is very possible thought that createing a seperate table as illustrated above (With proper index's)might well out perform combining the query).

Basically what is being done is I am adding all of the Transaction qty's from the dataset that have a rowID <= to the current row ID.

In order to do this You must be working with the same dataset (Hense why I opted to illustrate by creating a #tmp table over re-writing the same query 2 x's). If you opt To combine the query you can simply replace the #TMP from the second query with the main query above (Without the Into #Tmp line). Again check the statistics to verify it outperforms the alternatives mentioned.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

johnbeggs
Starting Member

2 Posts

Posted - 2008-09-12 : 19:06:14

Good article on Running Totals: http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-12 : 19:53:07

quote:
Originally posted by johnbeggs

Good article on Running Totals: http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

If that is the Jeff Moden article suggesting the set col=var=exp UPDATE syntax on a table with a clustered index, then that is a very good solution. Definately faster (over a large dataset) than the method (above). I have one nagging problem with it though. I don't believe sql server guarantees the order of the updates. Jeff's research certainly satisfies me that, under his restrictions, the updates do indeed follow the order of the clustered index. But I would not be too surprised if that didn't hold true in future versions.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources