How to get a previous column output as current column input?

Posted by Kirthiga under Sql Server on 2/13/2013 | Points: 10 | Views : 740 | Status : [Member] | Replies : 2
Hi,

Im having a table with 4 columns as below

Sno Opening Plusvalue MinusValue Closing
1 0 5 2 0
2 0 8 4 0
3 0 8 6 0
4 0 9 7 0

Formula for arriving my closing column is

Closing = Opening+Plusvalue-MinusValue

Formula for arriving my opening column is

Opening = Previous column closing

My required output is

Sno Opening Plusvalue MinusValue Closing
1 0 5 2 3
2 3 8 4 7
3 7 8 6 9
4 9 9 7 11

I need a query to acheive this output




Responses

Posted by: Oswaldlily on: 2/13/2013 [Member] Starter | Points: 25

Up
0
Down
Create table tbl1
(
sno numeric,
Opening numeric,
Plusvalue numeric,
MinusValue numeric,
Closing numeric
)
--insert into tbl1(sno,Opening,Plusvalue,MinusValue,Closing) values
-- (4,0,9,7,0)
declare @s_no numeric
declare @cl numeric
declare @pl numeric
declare @mi numeric
declare @op numeric

set @op=(Select Closing from tbl1 where SNO=@S_NO-1)
Set @s_no=(Select sno from tbl1 where sno=1)

set @pl=(Select Plusvalue from tbl1 where sno=1)
set @mi=(Select MinusValue from tbl1 where sno=1)
Select @cl=(Select Opening from tbl1 where sno=1) +(Select Plusvalue from tbl1 where sno=1)
-(Select MinusValue from tbl1 where sno=1)



Update tbl1 set Opening=@op,Plusvalue=@pl,MinusValue=@mi,Closing=@cl where sno=@s_no
select * from tbl1

Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 2/14/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
If Its a one time process!

UPDATE DotnetFunda SET CLOSING = (OPENING + PlusValue) - MinusValue

UPDATE OP SET OP.CLOSING = (OP.OPENING + OP.PlusValue) - OP.MinusValue,
OP.OPENING = ISNULL((SELECT C.CLOSING FROM DotnetFunda C WHERE OP.Sno = C.Sno+1),0)
FROM DotnetFunda OP
GO 4
Or, If Its a recurring process....! try with alternate way!

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response