Running Total Issue - 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 Issue

Author

Topic

pssumesh2003
Starting Member

35 Posts

Posted - 2013-03-06 : 08:17:28

I have a table following structure

Tank TrDate DaySINo QtyIn QtyOut AdjstmentQty ClosingStock
Tank1 5/1/2012 1 1000 0 0 Null
Tank1 5/2/2012 1 1000 500 0 Null
Tank1 5/3/2012 1 1000 0 0 Null
Tank2 5/1/2012 1 200 0 0 Null
Tank2 5/2/2012 1 200 500 0 Null
Tank2 5/3/2012 1 1000 0 0 Null

Given Opening day closingstock ( i mean for tank 1 closing stock is 2500 and that of tank2 is 1850). nw i want update all rows following formula closing stock of prev day + Qty in -Qty out -Adjustmentqty.

Please help me

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-06 : 08:28:55

How are you getting the start of day positions on the first of the month? Is there a table that has the positions?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-06 : 11:01:18

I understand that closing stock for Tank1 is = 2500 simply by "Sum(all QtyIN)-Sum(all QtyOut) of tank1", but how come the closing stock for tank2 = 1850 .. as per given data for tank2 it should be 1000+200+200-500=900?

Secondly, these are just calculations..why you wanting to have this updated in any column for all rows. Since you can do this calculation on the front end of an application

Cheers
MIK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-06 : 11:21:18

see scenario 1

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


;With CTE
AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY Tank ORDER BY TrDate) AS seq
FROM Table
)

SELECT Tank,TrDate,DaySINo,QtyIn,QtyOut,AdjstmentQty,RunValue
FROM CTE c1
CROSS APPLY (SELECT SUM(QtyIn) - SUM(QtyOut) AS RunValue
             FROM CTE
             WHERE Seq <= c.Seq
             AND Tank = c.Tank
             )c2

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources