Example: how often alarms occur

This query reports the average alarm rate on hourly basis.

DECLARE @StartTime as varchar(60)

DECLARE @EndTime as varchar(60)

SET @StartTime = '2015-10-25 12:00:00'

SET @EndTime = '2015-10-26 12:00:00'

DECLARE @AlarmRaise table

(

EventTime nvarchar(60),

ID nvarchar(50),

AlarmState nvarchar(20),

SourceArea nvarchar(20),

SourceObject nvarchar(20)

)

INSERT @AlarmRaise select EventTime,Alarm_ID,Alarm_State,Source_Area,Source_Object from Events where EventTime > @StartTime and EventTime < @EndTime and Alarm_State='UNACK_ALM'

--======================--

DECLARE @AlarmCounts table

(

ForDate nvarchar(60),

OnHour nvarchar(50),

CountperHour nvarchar(20)

)

INSERT @AlarmCounts SELECT CAST(EventTime as date) AS ForDate,

DATEPART(hour,EventTime) AS OnHour,

Count(*) AS "CountperHour"

FROM @AlarmRaise

GROUP BY CAST(EventTime as date),

DATEPART(hour,EventTime)

SELECT Avg(CAST(CountperHour as INT)) as "Average Alarm Rate on Hourly Basis" from @AlarmCounts

The resulting report looks like this: