-1

i am trying to achieve something as mentioned below

Row_Num    ID    Total Time     Timeout
----------------------------------------
1         33       120             1
2         34       120            121
3         35       121            241
4         36       145            362

using sql queries, i would like try to find the timeout from column, based on previous row total time. for every row_number 1 , timeout should be 1

eg: 1+120=121, 3rd row, 121+120=242 so on..

please help me in this regard. any help would be appreciated

Arjun
  • 11
  • 4

2 Answers2

0

This is simply a cumulative SUM, but you replace the first value with 1:

SELECT SUM(CASE RowNum WHEN 1 THEN 1 ELSE TotalTime END) OVER (ORDER BY RowNum) AS TimeOut

For example:

SELECT RowNum,
       TotalTime,
       SUM(CASE RowNum WHEN 1 THEN 1 ELSE TotalTime END) OVER (ORDER BY RowNum) AS TimeOut
FROM (VALUES(1,120),
            (2,120),
            (3,121),
            (4,145))V(RowNum,TotalTime);

Returns:

RowNum      TotalTime   TimeOut
----------- ----------- -----------
1           120         1
2           120         121
3           121         242
4           145         387

Appears the OP has changed their requirements, and the expected results are different now (/sigh). This would be:

SELECT RowNum,
       TotalTime,
       1 + ISNULL(SUM(TotalTime) OVER (ORDER BY RowNum ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) AS TimeOut
FROM (VALUES(1,120),
            (2,120),
            (3,121),
            (4,145))V(RowNum,TotalTime);

This, however, assumes that the OP's latest expected results are wrong, as 241+121=362 not 352.

Thom A
  • 88,727
  • 11
  • 45
  • 75
0

You can subtract out the first value and replace it with 1:

select t.*,
       1 + sum(total_time) over (order by row_num) - first_value(total_time) over (order by row_num) as total_time
from t;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • @Larnu . . . I realize based on your comment that the arithmetic in the question is way off and the question doesn't really make sense. – Gordon Linoff Nov 25 '20 at 15:31
  • All i need is second row timeout is calculated based on first row timeout and total time, third row timeout is sum of second row timeout and total time.. in this way I need to get using sql query – Arjun Nov 25 '20 at 15:41