Calculate a value using previous row value and current row value with condition in SQL-CodePudding

I have a problem. I want to create a calculated column call ExpectedOutcome .

The value of column ExpectedOutcome for 1st row where No.Session = 1 is calculated by Score 60. From the second row, the value of ExpectedOutcome = previous ExpectedOutcome Score.

The value of ExpectedOutcome is always in the range of 0 - 100. If it < 0, then we put 0 as the value. If it > 100, then we put 100 as the value.

It seems like calculate a running total, but it is not. And I do not know how to solve this problem. enter image description here

The database only has 3 columns named id, No.Session, Score

CodePudding user response：

Try This

Update @tb Set Outcome = 60   Score Where sesion = 1

Declare @Session decimal(18,0)
Declare F Cursor For Select sesion From @tb Where sesion <> 1 Order By sesion
Open F
Fetch Next From F Into @MaID
While @@FETCH_STATUS = 0
Begin   
    Update @tb 
        Set Outcome = Case When Score   (Select Outcome From @tb Where sesion = @Session - 1) > 100 Then 100
                            When Score   (Select Outcome From @tb Where sesion = @Session - 1) < 0 Then 0
                            Else Score   (Select Outcome From @tb Where sesion = @Session - 1) End
        Where sesion = @Session
    Fetch Next From F Into @Session
End 
Close F
DeAllocate F

CodePudding user response：

Here's a solution with recursive CTE:

with recursive cte (id, session_no, score, outcome) as (
select id, session_no, score, 60 score as outcome 
  from session_score 
 where session_no = 1
 union all
select ss.id, ss.session_no, ss.score,
       case
          when c.outcome   ss.score > 100 then 100
          when c.outcome   ss.score < 0 then 0
          else c.outcome   ss.score
       end as outcome       
  from session_score ss
  join cte c
    on ss.id = c.id
   and ss.session_no = c.session_no 1)
 select id, session_no, score, outcome
   from cte
  order by 1,2;

Tested with 2 IDs:

id    |session_no|score|outcome|
------ ---------- ----- ------- 
ST0127|         1|    2|     62|
ST0127|         2|    2|     64|
ST0127|         3|    2|     66|
ST0127|         4|    2|     68|
ST0127|         5|    2|     70|
ST0127|         6|    2|     72|
ST0127|         7|    2|     74|
ST0127|         8|    2|     76|
ST0127|         9|    2|     78|
ST0127|        10|    2|     80|
ST0127|        11|    2|     82|
ST0127|        12|    2|     84|
ST0127|        13|    2|     86|
ST0127|        14|    2|     88|
ST0127|        15|    2|     90|
ST0127|        16|    2|     92|
ST0127|        17|    2|     94|
ST0127|        18|    2|     96|
ST0127|        19|    2|     98|
ST0127|        20|    2|    100|
ST0127|        21|    2|    100|
ST0127|        22|    2|    100|
ST0127|        23|   -5|     95|
ST0127|        24|    2|     97|
ST0127|        25|    0|     97|
ST0127|        26|   -5|     92|
ST0127|        27|    2|     94|
ST0127|        28|    2|     96|
ST0127|        29|    2|     98|
ST0128|         1|    2|     62|
ST0128|         2|    2|     64|
ST0128|         3|    2|     66|
ST0128|         4|    2|     68|
ST0128|         5|    2|     70|
ST0128|         6|    2|     72|

CodePudding user response：

This will work correctly for the same id:

SELECT 
y.id,
y.nosession,
y.score,
@running_total := 
CASE WHEN @running_total   y.score > 100 THEN 100
WHEN @running_total   y.score < 0 THEN 0
ELSE @running_total   y.score END
AS cumulative_sum
FROM yourtable y
JOIN (SELECT @running_total := 60) r
ORDER BY y.id, y.nosession

This will do for different id's:

SELECT sub.id, sub.nosession, sub.score, sub.cumulative_sum
FROM
(SELECT 
y.id,
y.nosession,
y.score,
@running_total := 
CASE WHEN id <> @id THEN 60   y.score
WHEN @running_total   y.score > 100 THEN 100
WHEN @running_total   y.score < 0 THEN 0
ELSE @running_total   y.score END
AS cumulative_sum,
@id := y.id
FROM yourtable y
JOIN (SELECT @running_total := 60, @id:='0') r
ORDER BY y.id, y.nosession) sub;

Try out here: db<>fiddle