Printed 2017/07/27 07:35PM

Finding Date Ranges for Calculating Statistics

By Robert Davis, 2010/03/14

Finding Date Ranges for Calculating Statistics

I received the following question from a fellow DBA:

I need your input to derive the reliability of one of the tool I am currently working …


I have table which has outagedate column with dates like below

2009-12-05 04:21:00:000
2009-12-06 08:22:00:000
2009-12-10 01:11:00:000

And the requirement is to derive the reliability with below formula

Weekly App Reliability = e^(-168/<Mean Time Between outage in hours>)


Do you know how I can achieve the same with T-SQL?

My solution:

Use a recursive CTE to get the range of hours. The EXP() function is equivalent to e^().

In the sample code below, I’m using two CTE’s so that I can be assured that there is a 1 based unique number to use for iterating through the dates with no missing values in between. I am using the Row_Number() function to generate the OrderID values. For the sample code, I could have just used the identity column value, but in the real world, there will likely be values missing from the column plus the data may not necessarily be in order by date in the table.

Declare @Outages Table (

               OutageDateID int identity(1, 1) not null primary key,

               WeeklyOutageDate datetime



Insert Into @Outages (WeeklyOutageDate)

Values ('2009-12-05 04:21:00:000'),

       ('2009-12-06 08:22:00:000'),

       ('2009-12-10 01:11:00:000');


With Outs (OutageOrder, WeeklyOutageDate)

As (Select ROW_NUMBER() Over (Order By WeeklyOutageDate), WeeklyOutageDate

      From @Outages)

, OutRanges (OutageOrder, WeeklyOutageDate1, WeeklyOutageDate2, OutageRange)

As (Select OutageOrder, WeeklyOutageDate, WeeklyOutageDate,
      DateDiff(hour, WeeklyOutageDate, WeeklyOutageDate)

      From Outs

      Where OutageOrder = 1

      Union All

      Select O.OutageOrder, ORs.WeeklyOutageDate1, O.WeeklyOutageDate,
      DateDiff(hour, ORs.WeeklyOutageDate1, O.WeeklyOutageDate)

      From OutRanges ORs

      Inner Join Outs O On O.OutageOrder = ORs.OutageOrder + 1)

Select *,

      [Weekly App Reliability] = exp(-168/OutageRange)

From OutRanges

Where OutageRange > 0


Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.