Solved

Working with Multiple Date Time Columns in SQL Server.

Posted on 2011-02-23
2
317 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
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.
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 …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

911 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now