Having the schedule log table as below:
ScheduleLogID |ScheduleID |WorkerStatusID |StatusLogDatetime
------------------------------------------------------------------------
38 |202 |1 |2019-09-04 12:06:43.6216195
39 |202 |4 |2019-09-04 12:07:33.1519370
40 |202 |1 |2019-09-04 12:07:43.6976046
41 |202 |2 |2019-09-04 12:07:53.3540665
42 |202 |1 |2019-09-04 12:08:01.4505184
43 |202 |3 |2019-09-04 12:08:10.1397741
44 |202 |1 |2019-09-04 12:08:16.0210118
45 |202 |2 |2019-09-04 12:08:24.8038681
46 |202 |1 |2019-09-04 12:08:31.5119111
47 |202 |5 |2019-09-04 12:08:42.3227804
48 |224 |1 |2019-09-04 12:21:13.6859968
49 |224 |4 |2019-09-04 12:22:54.9930462
50 |224 |1 |2019-09-04 12:23:00.8586838
51 |224 |4 |2019-09-04 12:23:07.6291272
52 |224 |1 |2019-09-04 12:23:12.2779570
53 |224 |2 |2019-09-04 12:23:20.0936071
54 |224 |1 |2019-09-04 12:23:26.2556466
55 |224 |4 |2019-09-04 12:23:40.5921385
56 |224 |1 |2019-09-04 12:23:48.6417901
57 |224 |5 |2019-09-04 12:23:56.3482395
How can I calculate the total time spent in each Status for each Schedule ?
The result should look like this:
ScheduleID |WorkerStatusID |TimeSpent
--------------------------------------------
202 |1 |06:30:00
202 |2 |00:15:00
202 |3 |00:30:00
202 |4 |00:15:05
224 |1 |06:45:00
224 |2 |00:20:00
224 |3 |00:25:00
224 |4 |00:10:05
Try this (my numbers do not match your sample results):
with mytablelead(ScheduleLogID, ScheduleID, WorkerStatusID, StatusLogDatetime, leadtime) as
(
select ScheduleLogID, ScheduleID, WorkerStatusID, StatusLogDatetime,
lead(StatusLogDatetime) over(partition by ScheduleID order by StatusLogDatetime) as leadtime
from mytable
)
select ScheduleID, WorkerStatusID, sum(datediff(ss, StatusLogDatetime, leadtime)) as totalsecs
from mytablelead
group by ScheduleID, WorkerStatusID
order by ScheduleID, WorkerStatusID