Solved

Working with Multiple Date Time Columns in SQL Server.

Posted on 2011-02-23
2
325 Views
Last Modified: 2012-05-11
hi experts.

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)

Example.
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.

I.e.
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.

any ideas?
0
Comment
Question by:bl460c
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34968584
please see this sample script to check out what you need
create table tmp_ee ( id int, timedown datetime, timeup datetime)
GO

insert into tmp_ee values(1      ,convert(datetime, '22/02/2011 4:25:10 AM', 103),convert(datetime,'22/02/2011 10:30:30 AM', 103))
go
insert into tmp_ee values(2      ,convert(datetime,'23/02/2011 8:26:11 AM', 103),convert(datetime,'23/02/2011 8:26:43 AM', 103))
go
insert into tmp_ee values(3      ,convert(datetime,'28/02/2011 10:14:10 PM', 103),convert(datetime,'28/02/2011 11:30:30 PM', 103))
GO
declare  @report_start datetime
declare @report_end datetime

  set @report_start = convert(datetime, '22/02/2011 10:00 AM', 103)
set @report_end= convert(datetime, '23/02/2011 10:00 AM', 103)

; with data as (
SELECT t.*
   , case when t.timedown < @report_start then @report_start else t.timedown end report_timedown 
   , case when t.timeup > @report_end then @report_end else t.timeup end report_timeup   
 FROM TMP_ee t
where t.timeup >= @report_start
  and t.timedown <= @report_end
)
, downtime as (
select data.*
     , datediff(s, data.report_timedown, data.report_timeup) seconds_down     
  from data
)
select sum(seconds_down) down_time
     , datediff(s, @report_start, @report_end) report_time
     , 100 * cast(sum(seconds_down) as decimal(20,4)) / datediff(s, @report_start, @report_end) perc_down
     , 100 * ( 1- (cast(sum(seconds_down) as decimal(20,4)) / datediff(s, @report_start, @report_end))) perc_up
 from downtime
go
drop table tmp_ee

Open in new window

0
 

Author Closing Comment

by:bl460c
ID: 34976083
Excellent! Thanks!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question