?
Solved

Working with Multiple Date Time Columns in SQL Server.

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

850 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