0

I'd like to calculate in SQL server the cycle time in seconds of each ocurrance.

It's the difference between the current row 'TimeCol' and the following 'TimeCol'.

Screenshot

The desired answer is to have a calculated column that show the time spent on the current model.

TimeCol                          model_code              cycle_seconds
2021-07-08 16:02:25              9011                   185
2021-07-08 16:05:30              9016                   484623
2021-07-14 06:42:33              9016                   0
2021-07-14 06:42:33              9011                   31
2021-07-14 06:43:04

Any help will be much appreciated, Thanks.

Zhorov
  • 28,486
  • 6
  • 27
  • 52
Drac
  • 1
  • With lag, you compare to the previous row, I need the difference with the next row. Thanks for replying – Drac Jun 16 '22 at 11:37
  • ... The previous row is the next row if you reverse the order, @Drac ... Or, you could use `LEAD`. – Thom A Jun 16 '22 at 11:42
  • @Larnu thanks, it worked. Any idea how to make a new table/view so it includes this calculated value(T_Ciclo)? WITH cont AS ( SELECT *, rownum = ROW_NUMBER() OVER (ORDER BY TimeCol) FROM tbscxDraxtonTeruelProgeltaKunkelPouring ) SELECT cur.*,DATEDIFF(second,cur.TimeCol,next.TimeCol) T_Ciclo FROM cont cur INNER JOIN cont next on next.rownum = cur.rownum + 1 – Drac Jun 16 '22 at 14:25
  • A table can't use `LAG`/`LEAD` as a computed column, @Drac . You would need to use a `VIEW`. – Thom A Jun 16 '22 at 14:34

0 Answers0