Reporting when Logging only the start and end of an outage condition

In a previous post I had a system to only log the start and end of an outage condition.  To report on the outages we need to first look at the table where the exceptions get logged:

    CREATE TABLE [dbo].[Exceptions](
        [ExTime] [datetime] NULL CONSTRAINT [DF_Exceptions_ExTime]  DEFAULT (getdate()),
        [Message] [varchar](8000) NULL,
        [StackTrace] [varchar](8000) NULL
      ) ON [PRIMARY]

And a sample of the data:

        SELECT TOP 12 ExTime, Substring(Message,1,60) Message
        FROM Exceptions

 Now to do the reporting we create a temp table with just the records we are interested in:

           select ExTime,
           case when charindex(‘start of’,Message) > 0 then ‘S’
           else ‘E’ end a,
           substring(Message,charindex(‘Guid’,Message)+5,40) g  into #t
           from Exceptions
           where Message like ‘Outage%’

The first column (badly named “a”) is just a flag to say if it is a Start or End record.  The second column (named “g”) is the Guid for the outage event.    

Now create another temp table where the guid is summarized:

        select g, max(case when a = ‘S’ then ExTime end) startTime,
        max(case when a = ‘E’ then ExTime end) endTime into #t2
        from #t
        group by g
        having count(1) = 2

Now we can report on this.

           select startTime,
           CASE DATEPART(weekday,startTime)
           WHEN 1 THEN ‘Sunday’
           WHEN 2 THEN ‘Monday’
           WHEN 3 THEN ‘Tuesday’
           WHEN 4 THEN ‘Wednesday’
           WHEN 5 THEN ‘Thursday’
           WHEN 6 THEN ‘Friday’
           WHEN 7 THEN ‘Saturday’
           END,
           round((datediff(ss,startTime,endTime)+0.0)/60,2) Minutes
           from  #t2 t
           where not exists
           (     select 1
                 from #t2 tin
                 where t.startTime between tin.startTime and tin.endTime
                 and t.g <> tin.g)
          and startTime > ’05/13/09′
          order by startTime

  This gives us the following report that shows us the start time of the outage and how many minutes it lasted: