Querying Downtime Duration

A common need in querying downtime is to select all downtime for a given period of time but only report the duration of the downtime that is fallen within the reporting period. In these situations, the start of the reporting period falls between the start and end time of the downtime record.

The following query will return all downtime records that fall within a given reporting period, even if the Start_Time or End_Time is outside of it and, by using a Case statement, restricts the calculation of duration to within the reporting period.

SELECT datediff(s, CASE  WHEN Start_Time < @ReportStartTime THEN @ReportStartTime
                         ELSE Start_Time END,
                   CASE  WHEN	End_Time > @ReportEndTime
                             OR End_Time IS NULL	THEN @ReportEndTime 
                         ELSE End_Time
                         END)	                        AS Duration
FROM Timed_Event_Details 
WHERE	   PU_Id = @PUId
           AND Start_Time < @ReportEndTime 
           AND (	End_Time > @ReportStartTime
                        OR End_Time IS NULL)