Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Working with Multiple Date Time Columns in SQL Server.

Posted on 2011-02-23
2
Medium Priority
?
367 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 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

636 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