SQL Running values, Previous value Question

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)

SQL Running values, Previous value Question

Author

Topic

nhell
Starting Member

6 Posts

Posted - 2009-04-06 : 04:07:36

Hello. Good day. I am new in administering database. I use SQL Server 2005. Our project is POS system. My Problem is Calculating running values with a simple twist.

Date--------Movementtype---In-----Out----OnHand
03/29/09----Receive--------20------0-------55
03/28/09----TransferOUT-----0-----10-------35
03/23/09----TransferOUT-----0------5-------45
03/22/09----Adjustment----(50)-----0-------50
03/21/09----Receive--------20------0------130
03/20/09----TransferOut-----0-----20------110
03/19/09----Receive--------30------0------130
03/18/09----Adjustment---(100)-----0------100
03/16/09----Receive--------10------0-------10

Arrange in Descending order by date,I want to calculate all running values of normal transactions. And the "Adjustment" is the replacement of value in the inventory. "OnHand" is not included in my fields means that was just a calculated column based on "in" and "out". I was doing this in a stored procedure and SELECT statement is my only choice to show the results and I'll pass it to reporting services.

We use this to show manual computation of itemmovement on warehouse.

I am sorry if I am asking a very easy question for you. I started using sql server 2005 for just 3 months and I am just in entry level. Hope someone can help me with this. Thank you..

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-04-06 : 04:26:00

If your table has an Identity column (eg:myId ) then, the below query will give your desired result

;with cte as
(
select top 1 myID,Date,MovementType,[In],Out,[In] - out as Remaining
from Product
union all
select p.myID,p.Date,p.MovementType,p.[In],p.Out,cte.Remaining + P.[In] - P.out
from cte
inner join Product P on cte.myId + 1 = P.myId
)
select * from cte

If your table don't have any identity column, then use the below query

with cte1 as
(
select Date,MovementType,[In],Out,row_number() over (order by date) as myID from Product
)
,
cte as
(
select top 1 myID,Date,MovementType,[In],Out,[In] - out as Remaining
from cte1
union all
select p.myID,p.Date,p.MovementType,p.[In],p.Out,cte.Remaining + P.[In] - P.out
from cte
inner join cte1 P on cte.myId + 1 = P.myId
)
select * from cte

nhell
Starting Member

6 Posts

Posted - 2009-04-06 : 04:54:16

I don't have an identity column. Because this table was a view table derived from a table with different transactions. I see a light on your reply but my main problem is the "adjustment" movementtype where it replace the Previous count to a new initial count. If I use case function, How come I can get the previous value of my "OnHand"

Thank you very much bro for a quick solution.. Take care.

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-04-06 : 05:02:55

Then use this query.

with cte1 as
(
select Date,MovementType,[In],Out,row_number() over (order by date) as myID from Product
)
,
cte as
(
select top 1 myID,Date,MovementType,[In],Out,[In] - out as Remaining
from cte1
union all
select p.myID,p.Date,p.MovementType,p.[In],p.Out,cte.Remaining + P.[In] - P.out
from cte
inner join cte1 P on cte.myId + 1 = P.myId
)
select * from cte

I think Case statement will not do any help. Just do like how i approached in the above query.

nhell
Starting Member

6 Posts

Posted - 2009-04-06 : 05:10:59

Thanks brother for your help. I really appreciate it.

nhell
Starting Member

6 Posts

Posted - 2009-04-06 : 05:55:16

My problem now is how can I replace the running value everytime I will encounter "Adjustment" movement type..

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-04-06 : 06:09:43

OOps, I didn't observe that "Adjustment" case previously. Then use the below query
with cte1 as
(
select Date,MovementType,[In],Out,row_number() over (order by date) as myID from Product
)
,
cte as
(
select top 1 myID,Date,MovementType,[In],Out,

case
when MovementType = 'Adjustment' then [In]
else [In]- out

end as Remaining
from cte1
union all
select p.myID,p.Date,p.MovementType,p.[In],p.Out,
case
when p.MovementType <> 'Adjustment' then cte.Remaining + P.[In] - P.out
else p.[In]

end

from cte
inner join cte1 P on cte.myId + 1 = P.myId
)
select * from cte

nhell
Starting Member

6 Posts

Posted - 2009-04-06 : 19:53:27

wow. nice. thanks bro. I'll try your idea and make my own style. At least I have an idea how I will work for it. Thank you very much..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources