SQL: value equal to difference between a column in this row and another column in previous row-CodePudding

I have a timesheet dataset for which I only have the start time of the first of multiple activities for a staff member in a day, but I do have the qty minutes spent on each of the activities. I would like to generate a dataset that contains start and end times for each f the activities.

So the Start Time in row two must be = End Time in row one. End Time in Row two must be the newly calculated Start time for row two Minutes in row two, and so on for the rest of the rows. I tried options of joining with the same table on t1.ActivityOrder = t2.ActivityOrder 1 but this did not work.

How can I go about doing this?

CodePudding user response：

Since there is no difference between end time of previous an start time of current you only need to know the start time of task order 1 and the cumulative durations

for example given

create table t
(id int,startdt smalldatetime, enddt smalldatetime, duration int, taskorder int);

insert into t values
(1,'2022-10-06 07:00:00',null, 20,1),
(2,null,null, 10,2),(3,null,null, 10,3),(4,null,null, 10,4)

select id, 
       case when startdt is null then
        dateadd(minute,sum(duration) over (order by taskorder ROWS BETWEEN UNBOUNDED PRECEDING AND 1 preceding ) ,
        (select startdt from t t1 where t1.taskorder = 1))
        else startdt
       end startdt,
       case when enddt is null then
        dateadd(minute,sum(duration) over(order by taskorder),
        (select startdt from t t1 where t1.taskorder = 1))
       end enddt,
      duration,
      taskorder,
      sum(duration) over (order by taskorder) sumtask,
      sum(duration) over (order by taskorder ROWS BETWEEN UNBOUNDED PRECEDING AND 1 preceding ) sumprecd
from t

id          startdt                 enddt                   duration    taskorder   sumtask     sumprecd
----------- ----------------------- ----------------------- ----------- ----------- ----------- -----------
1           2022-10-06 07:00:00     2022-10-06 07:20:00     20          1           20          NULL
2           2022-10-06 07:20:00     2022-10-06 07:30:00     10          2           30          20
3           2022-10-06 07:30:00     2022-10-06 07:40:00     10          3           40          30
4           2022-10-06 07:40:00     2022-10-06 07:50:00     10          4           50          40

(4 row(s) affected)