Query for total downtime of site using datestamps and status columns what want to find the total downtime for a site. I'm thinking I can subtract the 'down' DateStamp from the following 'up' DateStamp. then add the totals together for all downtime.



I want to get a year to date for total downtime for any particular site (website). I'm using a sitemonitor to register downtime with a timestamp called datetime. It's the start of the downtime and the following up is when the downtime ended.

There can be multiple down times. The table is an example of what the data looks like.

The downtime may not be in 15 second increments. It could be 10 second to 45 seconds. It would be the difference between the downtime and the following uptime in the table. Is this even possible to be done?
Would this work.  I just had the query give the totals for each year.  You could easily filter by date if you wanted to restrict it for just this year, or a specific year you are looking at.

Also, this assumes the final record in the table is an up time.  If you end with the site being down, then that last downtime isn't included.  You should get your site back up before worrying about calculating downtime :).

WITH TAB
AS
(
    SELECT * ,
           Row_Number() OVER (Partition BY Site ORDER BY DateTime) AS RowNum
    FROM @Table
)
SELECT UT.Site , DATEPART(Year, UT.DateTime) as Year,
       Sum(DateDiff(second, DT.DateTime, UT.DateTime)) as TotalDownTimeSeconds
FROM       Tab AS DT
INNER JOIN Tab AS UT
    ON DT.SiteStatus = 'Down'
    AND UT.RowNum = DT.RowNum + 1
    AND UT.Site = DT.Site
WHERE DT.Site = 'ZooToys'
GROUP BY UT.Site, DATEPART(Year, UT.DateTime);


If you want to get the down time for the past 60 days, add to the where clause: 

UT.DateTime &gt; DateAdd(day, -60, getdate())