How to update value from previous row

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

I have a table like this

C#

I want a result like this

C#

(ie) If the NetAmt value is 0 I have to update the previous row value upto next non zero value.

Posted 26-Nov-15 19:59pm

kirthiga S

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Animesh Datta · Answer 1 · 2015-11-26T21:47:00

Hello ,
Try this way

create table #tmp //Creating one Temporary table
(
sno int,
netamt decimal(10,2)
)

//Inserting records
insert into #tmp values
(1,0),
(2,0),
(3,	-10761),
(4,	0),
(5,	38834),
(6,	0),
(7,	0),
(8,	-100000),
(9,	0),
(10,	80000),
(11,	28457),
(12,	0),
(13,	0),
(14,	0),
(15,	0)

Now make the logic by simple loop

DECLARE @totalRecords INT
DECLARE @I INT

SELECT @I = 1
SELECT @totalRecords = COUNT(sno) FROM #tmp

WHILE (@I <= @totalRecords) //let take one while loop to check the record count
BEGIN

	declare @curval decimal(8,2)
	declare @nextval decimal(8,2)
	declare @preval decimal(8,2)
	select @curval=netamt from #tmp where sno=@I

	IF (@I<@totalRecords) //for all record except last record , check whether the next value is 0 or not , if 0 then replace by previous value
		BEGIN
			declare @j int			
			set @j=@I+1
			select @nextval=netamt from #tmp where sno=@J
			if(@nextval=0)
				begin
					update #tmp set netamt=@curval where sno=@j
				end
			
		END
	ELSE if (@I=@totalRecords) //for last record  , check whether the current value is 0 or not , if 0 then replace by previous value
		BEGIN
			declare @k int			
			set @k=@I-1
			select @preval=netamt from #tmp where sno=@k
			if(@curval=0)
				begin
					update #tmp set netamt=@preval where sno=@i
				end			
		END
		SELECT @I = @I + 1	 // variable increment 
END

The O/P will be

C#

sno	netamt
1	0.00
2	0.00
3	-10761.00
4	-10761.00
5	38834.00
6	38834.00
7	38834.00
8	-100000.00
9	-100000.00
10	80000.00
11	28457.00
12	28457.00
13	28457.00
14	28457.00
15	28457.00

Thanks

Sums Mohs Eds · Answer 2 · 2015-11-26T21:58:00

Solution 2

The above would be obtained in a query if the ID would have been accordingly.
In this case, I would approach it with a Stored procedure and a cursor.

so pseudo code will be

previousval=0;

Start cursor

If(previousval<>NetAmt)
if(NetAmt=0)
update set Netamt =previousval where id=@id
End If
previousval=NetAmt
End if

End Cursor

Posted 26-Nov-15 21:58pm

Sums Mohs Eds

Comments

kirthiga S 27-Nov-15 4:03am

Is there any posible way using cte

Sums Mohs Eds 27-Nov-15 5:04am

Thats what i have mentioned at the start of the solution, if the ID was accordingly you would have. That is, you should be grouping it.

For Example,

SNo NetAmt
1 0
2 0
3 -10761
3 0
4 38834
4 0
4 0
5 -100000
5 0
6 80000
7 28457
7 0
7 0
7 0
7 0

With the above grouping we can still try using CTE.

But then since you have tried using while loop, below is the modified solution which works.

DECLARE @totalRecords INT
DECLARE @I INT

SELECT @I = 1
SELECT @totalRecords = COUNT(sno) FROM tmp1
declare @preval decimal(8,2)
set @preval=0
WHILE (@I <= @totalRecords)
BEGIN

declare @curval decimal(8,2)
declare @nextval decimal(8,2)

select @curval=netamt from tmp1 where sno=@I

if(@preval<>@curval)
Begin
If (@curval=0)
Begin
update tmp1 set netamt=@preval where sno=@I
End
End

if(@curval<>0)
Begin
Select @preval=@curval
End

set @I=@I+1;
END

Sums Mohs Eds · Answer 3 · 2015-11-26T23:05:00

Posting the same Solution of comments here with proper highlights of SQL.

SQL

DECLARE @totalRecords INT
DECLARE @I INT

SELECT @I = 1
SELECT @totalRecords = COUNT(sno) FROM tmp1
declare @preval decimal(8,2)
set @preval=0
WHILE (@I <= @totalRecords) 
BEGIN

declare @curval decimal(8,2)
declare @nextval decimal(8,2)

select @curval=netamt from tmp1 where sno=@I


if(@preval<>@curval)
Begin
If (@curval=0)
Begin
update tmp1 set netamt=@preval where sno=@I
End
End

if(@curval<>0)
Begin
Select @preval=@curval
End

set @I=@I+1;
END

How to update value from previous row

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0