June 2, 2004 at 1:33 pm
|
Could someone please help since I'm in dark now. I have a query result like this: Item Day Inhand Demand EndingBal Ind A 0 50 0 50 1 A 1 20 0 0 2 A 2 0 0 0 3 B 0 10 0 10 4 B 1 10 0 0 5 B 2 0 0 0 6 I want to calculate the endingbal: EndingBal = EndingBal+Inhand-Demand I write a statement like this: SELECT item,day, demand, isnull((select sum(Endingbal) from mytable a where a.item=b.item and a.idx<b.idx),0)+isnull(inhand,0)-isnull((select sum(demand) from mytable a where a.item=b.item and a.idx<=b.idx ),0) as EndBal, idx FROM mytable b The above statement gave me this: Item Day Inhand Demand EndingBal Ind A 0 50 0 50 1 A 1 20 0 70 2 A 2 0 0 50 3 B 0 10 0 10 4 B 1 10 0 20 5 B 2 0 0 10 6 It calculated the endingbal forward incorrectly, and I don't know what I did wrong. Please advice. Thanks for million. Minh Vu
|
June 3, 2004 at 3:13 pm
Sorry, I don't understand exactly your question... but why do you not use a function?
If you explan me you question, maybe I could help you. I don't understand how you obtain EndingBal...
EndingBal = EndingBal+Inhand-Demand
June 3, 2004 at 5:48 pm
|
I tried to achieve the result that looks like this: Item Day Inhand Demand EndingBal Ind A 0 50 0 50 1 A 1 20 0 70 2 A 2 0 0 70 3 B 0 10 0 10 4 B 1 10 0 20 5 B 2 0 10 10 6 I want to calculate my endingBal. Endingbal= previous record's endingbal+current record's inhand-current record's demand. I don't know how to write a loop that can help me to update the endingbal field as I process each record. Thus each day will have to be a separate select statement until the end of the result set. Thanks Minh |
June 4, 2004 at 2:02 am
create table mytable
( item char(1) , day int , inhand int , demand int , endingbal int, ind int identity (1,1 ) )
GO
insert mytable
select 'A',0 , 50, 0, 0
insert mytable
select 'A',1 , 20, 0, 0
insert mytable
select 'A',2 , 0, 0, 0
insert mytable
select 'B',0 , 10, 0, 0
insert mytable
select 'B',1 , 10, 0, 0
insert mytable
select 'B',2 , 0, 10, 0
GO
select item, day , inhand , demand , endingbal = (select sum ( a.inhand - a.demand ) from mytable a where a.item = b.item and a.ind<=b.ind )
, ind
from mytable b
June 4, 2004 at 11:42 am
|
Hi Amit, That's terrific! I couldn't believe that it could be done. I thought that I had to write a loop statement. Thank you very much. It's exactly what I tried to achieve. Before I read your solution, I rewrite my script to : SELECT item, day, inhand,demand, EndBal = endingBal+select sum(inhand) from mytable a where a.item=b.item and a.ind<=b.ind) -(select sum(demand) from #mytable a where a.item=b.item and a.ind<=b.ind),0), ind FROM mytable b This works 95% and I had to run an extra update statement to reset my endingbal to 0 if day =0, but yours works 100%. Again, thank so much for such great way to solve the puzzle. Minh |
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply