Subscribe to this blog
Briefcase
Print
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:000And 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, From Outs Where OutageOrder = 1 Union All Select O.OutageOrder, 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
DateDiff(hour, WeeklyOutageDate, WeeklyOutageDate)
DateDiff(hour, ORs.WeeklyOutageDate1, O.WeeklyOutageDate)
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.