How to get the current row value based on previous row value in sql server- ex first row has value, calculate the second row value using first row and some conditions and 3rd using 2nd row and same conditions

Question

when basin <> prebasin, numberinbasinnew=numberinbasin. when basin=prebasin use the numberinbasinnew from the first row (basin<>prebasin) and calculate the second row value for numberinbasinnew based on below conditions

       --    CASE

       --        WHEN PreValue >= 1

       --             OR

       --             (

       --                 Value > 0

       --                 AND

       --                 (

       --                     Value >= 1

       --                     OR CEILING(PrevCumulativeBasin) = PrevCumulativeBasin

       --                     OR CEILING(CumulativeBasin) != CEILING(PrevCumulativeBasin)

       --                 )

       --             ) THEN

       --            PrevNumberInBasinNew + 1   

       --        ELSE

       --            PrevNumberInBasinNew 

       --    END

   END AS NumberInBasinNew   here PrevNumberInBasinNew  need to calculate for each row and for each basin

Answer 1

Check a query:

;
with Q1 as
(
	select *, row_number() over (partition by basin order by NumberInBasin) as n
	from MyTable
),
Q2 as
(
	select basin, prebasin, NumberInBasin, [Value], CumulativeBasin, NumberInBasinNew=NumberInBasin, n
	from Q1
	where n = 1
	union all
	select Q1.basin, Q1.prebasin, Q1.NumberInBasin, Q1.[Value], Q1.CumulativeBasin, 
		NumberInBasinNew = 
			CASE WHEN Q2.[Value] >= 1
					OR 
					(
						Q2.[Value] > 0
						AND
						(
							Q2.[Value] >= 1
							OR CEILING(Q2.CumulativeBasin) = Q2.CumulativeBasin
							OR CEILING(Q1.CumulativeBasin) != CEILING(Q2.CumulativeBasin)
						)
					) THEN
					Q2.NumberInBasinNew + 1   
				ELSE
					Q2.NumberInBasinNew 
			END, 
		Q1.n
	from Q2
	inner join Q1 on Q1.basin=Q2.basin and Q1.n = Q2.n+1
)
select basin, prebasin, NumberInBasin, [Value], CumulativeBasin, NumberInBasinNew
from Q2
order by basin, NumberInBasin
option (maxrecursion 0)

If it does not work, show the mistake and the expected results.

It can be converted to UPDATE if needed.

Answer 2

Maybe a cursor will work faster. Make sure that the table contains a primary key (or unique index on basein and NumberInBasin), and try this:

declare c cursor forward_only for
	select t.basin, t.prebasin, t.NumberInBasin, t.[Value], t.CumulativeBasin,
	t1.[Value] as PreValue, t1.CumulativeBasin as PrevCumulativeBasin, t1.NumberInBasinNew as PrevNumberInBasinNew
	from MyTable t
	left join MyTable t1 on t1.basin = t.basin and t1.NumberInBasin < t.NumberInBasin
	left join MyTable t2 on t2.basin = t.basin and t2.NumberInBasin > t1.NumberInBasin and t2.NumberInBasin < t.NumberInBasin
	where t1.basin is null or t2.basin is null
	order by t.basin, t.NumberInBasin
	for update of t.NumberInBasinNew

declare @basin varchar(max)
declare @prebasin varchar(max)
declare @NumberInBasin int
declare @Value float
declare @CumulativeBasin float
declare @NumberInBasinNew int
declare @PreValue float
declare @PrevCumulativeBasin float
declare @PrevNumberInBasinNew int

open c

while 7=7
begin

	fetch next from c
	into @basin, @prebasin, @NumberInBasin, @Value, @CumulativeBasin, @PreValue, @PrevCumulativeBasin, @PrevNumberInBasinNew

	if @@FETCH_STATUS <> 0 break

	if @basin <> @prebasin 
		set @NumberInBasinNew = @NumberInBasin
	else
		set @NumberInBasinNew = 
		 CASE
             WHEN @PreValue >= 1
                  OR
                  (
                      @Value > 0
                      AND
                      (
                          @Value >= 1
                          OR CEILING(@PrevCumulativeBasin) = @PrevCumulativeBasin
                          OR CEILING(@CumulativeBasin) != CEILING(@PrevCumulativeBasin)
                      )
                  ) THEN
                 @PrevNumberInBasinNew + 1   
             ELSE
                 @PrevNumberInBasinNew 
         END

	update MyTable
	set NumberInBasinNew = @NumberInBasinNew
	where current of c

end

close c deallocate c