Hello ,
Try this way
create table #tmp
(
sno int,
netamt decimal(10,2)
)
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)
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)
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)
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
END
The O/P will be
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