Solved

Working with Multiple Date Time Columns in SQL Server.

Posted on 2011-02-23
2
350 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 143

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

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…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

717 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