## SQL Server Soldier :: News from the frontlines of the database wars

### Finding Date Ranges for Calculating Statistics

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

WeeklyOutageDate
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

##### Posted by Steve Jones on 15 March 2010

Very nice technique. This could be handy in a number of places where there are dates being stored and someone needs to calculate intervals.

##### Posted by wildh on 17 March 2010

This is probably the best approach if you have less than just over 32k records. CTE’s have a max recursion (which is rather frustrating). I found this out when trying to do something similar. We have a feedback table that tells us when every process start and finish, we wanted to be able to view how long in any given day the server was active on these processes, the first approach was to identify every second of the day the server was active with these processes and then use a CTE to derive the active ranges. Unfortunately, the limitations of CTE meant we couldn’t use this approach.

##### Posted by richardd on 17 March 2010

You could avoid the recursion by simply joining the first CTE to itself:

Outs As OutStart INNER JOIN Outs As OutEnd

On OutStart.OutageOrder = OutEnd.OutageOrder - 1

For this simple dataset, the execution plan for this approach looks marginally better than the recursive approach.

##### Posted by Robert Davis on 19 March 2010

Thanks Steve, wildh, and richardd!! Great feedback!!

##### Posted by gregsoc on 4 August 2012

I found this solution and it is something I can use.. GREAT!!   But.. Not sure how to implement the non-recusive solution being proposed by richardd.  Where does the code snippet he has listed get inserted in the original code?  Any help would be great.

##### Posted by gregsoc on 4 August 2012

I found this solution and it is something I can use.. GREAT!!   But.. Not sure how to implement the non-recusive solution being proposed by richardd.  Where does the code snippet he has listed get inserted in the original code?  Any help would be great.