Querying the StateSummaryHistory view

The StateSummaryHistory view is a "wide" view that allows you to return multiple statistics for a single tag within a single query.

The following query returns the state count, total time in state, and the percentage of time in state for the SysPulse system tag for the last hour. One row is returned for each state.

DECLARE @End DateTime

SET @END = getdate()

SELECT

TagName,

Value,

OPCQuality,

StateCount,

StateTimeTotal,

StateTimePercent

FROM dbo.StateSummaryHistory

WHERE TagName = N'SysPulse'

AND StartDateTime >= dateadd(minute,-60,@End)

AND EndDateTime <= @End

AND wwCycleCount = 1

The results are:

The following query returns the minimum time in state, the minimum contained time in state, and value for the SysTimeSec system tag.

SELECT

TagName,

StartDateTime,

EndDateTime,

StateTimeMin as STM,

StateTimeMinContained as STMC,

Value

FROM StateSummaryHistory

WHERE TagName='SysTimeSec'

AND wwRetrievalMode='Cyclic'

AND wwResolution=5000

AND StartDateTime>='2009-10-21 17:40:00.123'

AND StartDateTime<='2009-10-21 17:40:05.000'

The results are: