Say you have a room with an indefinite number of light bulbs, and these are turning randomly on and off. Each time a bulb is turned on and then off, a record is entered in a table with TurnedOn and TurnedOff values. How should the query look like if I am interested in how long (HH.mm.ss) was it visible in the room between two DateTime values?

e.g.

So for the example above in the time period between 2022-10-01 06:00:00 and 2022-10-01 15:00:00 - 09 hours has passed and it was visible for 07 hours.

1. The bulb can be on for more than 24 hours.
2. One hour increments are put in the example for simplicity.
3. If at least one light bulb is on, you can see in the room.
4. If a Light is turned on, starting from that moment you can see in the room, and if a light is turned off starting from that moment you can not :-)

Another example with the same logic:

Say you have a machine that more than one person can work on at the same time. StartTime and EndTime is added to the table each time when a person starts and then stops working on a machine. I am interested in what was machines work time for a given time period?

CodePudding user response：

select   sign(on_off)   as on_off
        ,sum(hour_diff) as hours
from
(
select   *
        ,datediff(second, time, lead(time) over(order by time))/3600.0 as hour_diff
        ,sum(case when status = 'TurnedOn' then 1 else -1 end) over(order by time) as on_off

from     t
unpivot  (time for status in(TurnedOn, TurnedOff)) up
) t
group by sign(on_off)

Fiddle

CodePudding user response：

To expand on the correct answer above, this will give the results in the desired hh:mm:ss format

select   
    sign(on_off)   as on_off,
    RIGHT('0' CONVERT(VARCHAR(2),SUM(secs_diff)/3600 ),2) 'h'
     RIGHT('0' CONVERT(VARCHAR(2),SUM(secs_diff)/60 ` ),2) 'm'
     RIGHT('0' CONVERT(VARCHAR(2),SUM(secs_diff)`),2) 's'
from
(
select   *
        ,datediff(second, time, lead(time) over(order by time))as secs_diff
        ,sum(case when status = 'TurnedOn' then 1 else -1 end) over(order by time) as on_off

from     #Lights
unpivot  (time for status in(TurnedOn, TurnedOff)) up
) t
group by sign(on_off)