Calculating Uptime

Uptime is the time between the Start_Time of one event and the End_Time of the previous event. There is no way to join the previous event directly out of the Timed_Event_Details table so a temporary table must be used. The following code demonstrates the best way to calculate uptime.

DECLARE @Downtime TABLE (
DowntimeId	int PRIMARY KEY IDENTITY,
StartTime	datetime,
EndTime	        datetime,
Downtime	int,
UpTime	        int)

INSERT @Downtime (	StartTime,
                        EndTime,
                        Downtime)
 
SELECT	Start_Time, 
        End_Time,
 

       datediff(s, Start_Time, End_Time) 
FROM Timed_Event_Details
WHERE	      PU_Id = @PUId
              AND Start_Time < @ReportEndTime 
              AND (End_Time > @ReportStartTime
                      OR End_Time IS NULL) 
ORDER BY Start_Time ASC
UPDATE d1
SET Uptime = datediff(s, d2.EndTime, d1.StartTime) 
FROM @Downtime d1
  INNER JOIN @Downtime d2 ON d2.DowntimeId = (d1.DowntimeId - 1) 
  WHERE d1.DowntimeId > 1

SELECT  StartTime, 
        EndTime, 
        Downtime, 
        Uptime
FROM @Downtime