Working with Multiple Date Time Columns in SQL Server.
Posted on 2011-02-23
I'm looking for help with an SQL query.
I have a SQL table that logs website outages.
It has two datetime columns (Timedown and Timeup)
ID timedown timeup
-- -------- ------
1 22/02/2011 4:25:10 AM 22/02/2011 4:30:30 AM
2 23/02/2011 8:26:11 AM 23/02/2011 8:26:43 AM
3 29/02/2011 10:14:10 PM 29/02/2011 11:30:30 PM
I want to create a SQL query / stored procedure that allows me to calculate the percentage uptime of the website between two selected datetimes. (I.e. between 20/02/2011 1:00 AM and 25/02/2011 5:00 AM, the percentage uptime was 99.98%)
but to get an accurate reading, it needs to calculate the difference between values when an @reportstart or @reportend (or both) parameter is passed that falls not only around the timedown and timeup, but also DURING the outage.
timedown - 2:00 PM, timeup - 5:00 PM,
reportstart - 3:00 PM, reportend 9:00 PM.
so.. the downtime hours during selected report period (3:00 PM to 9:00 PM) is reported as 2 Hours, (not 3).
percentage uptime = 100 - (total hours in selection that website is down = 2 / total hours in selection = 6 * 100 ) = 66.7 % uptime etc
Having trouble constructing a query that can do this.