I received the following question from a fellow DBA:

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