Haminteu Haminteu - 5 months ago 13
SQL Question

Read next record "NOT" previous record sql

I have the following t-sql:


declare @startno int = 1, @finishno int = 365, @AfterO float = 97
declare @daysbet int = 80, @decVal float = 0.10 / 100;

;with ctetest as
(
select @startno as oDay, 1 as oBet, @AfterO as Prod
union all
select oDay +1, case when ((oBet + 1) = @daysbet) then 0 else (oBet + 1) end,
case when (Prod = 0) then @AfterO else
case when oBet = 0 then 0 else (Prod - (@decVal * Prod)) end
end
from ctetest
where oDay+1 <= @finishno
)
select * from ctetest option (maxrecursion 0)


the result is:


----------------------------
oDay oBet Prod
----------------------------
1 1 97
2 2 96.903
3 3 96.806
ff.----->
80 0 89.628
81 1 0
82 2 97
ff.----->
364 44 93.008
365 45 92.915


But the result that I want is not like that, the result should be like this:





oDay oBet Prod
----------------------------
1 1 97
2 2 96.903
3 3 96.806
ff.----->
80 0 0
81 1 97
82 2 96.903
ff.----->
364 44 92.9
365 45 92.8


from the formula above, it seems to be that
case when oBet = 0
read the previous record in
oBet
column. I want to read the value next to
Prod
column in
oBet
column.


Please advice. Thank you.

Answer

Change

case when oBet = 0 then 0 else (Prod - (@decVal * Prod)) end

to

case when ((oBet + 1) = @daysbet) then 0 else (Prod - (@decVal * Prod)) end

Since oBet only actually becomes 0 in the next iteration (at oDay=81)