We need to find out how many hours for day = 1/2/2020 was the machine running.
State 1 = Started Computer 0 = Stopped Computer
| id | State | EventTimestamp |
|---|---|---|
| b | 1 | 1/3/2017 14:00 |
| a | 1 | 1/2/2020 14:00 |
| a | 0 | 1/2/2020 18:00 |
| a | 1 | 1/2/2020 23:00 |
| a | 0 | 1/3/2020 14:00 |
Calculate computer usage on a particular day [SQL]
Viewed 39
1 Answers
you could try this
select id,hoursrunning from
(
select id, state,
datediff(
hours,
LEAD(EventTimestamp,1,'1/2/2020 24:00') OVER (partition by id order by EventTimestamp ),
EventTimestamp ) as hoursrunning
from Instance_Dim
where date(EventTimestamp) = '1/2/2020')T
where state=1
Related