SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Running Total Issue		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pssumesh2003
Starting Member

India
32 Posts
Posted - 03/06/2013 :  08:17:28  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1731 Posts
Posted - 03/06/2013 :  08:28:55  Show Profile  Reply with Quote
How are you getting the start of day positions on the first of the month? Is there a table that has the positions?
Go to Top of Page

MIK_2008
Aged Yak Warrior

Pakistan
926 Posts
Posted - 03/06/2013 :  11:01:18  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48071 Posts
Posted - 03/06/2013 :  11:21:18  Show Profile  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000