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

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

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

Latest Posts