Average retrieval - query examples

To use the average mode, set the following parameter in your query.

wwRetrievalMode = 'Average'

Query 1

The time-weighted average is computed for each of five 1-minute long cycles.

Note that the wwTimeStampRule parameter is set to "Start" in the query. This means that the value stamped at 11:18:00.000 represents the average for the interval 11:18 to 11:19, the value stamped at 11:19:00.000 represents the average for the interval 11:19 to 11:20, and so on. If no timestamp rule is specified in the query, then the default setting in the TimeStampRule system parameter is used.

In the first cycle there are no points, so a NULL is returned. In the second cycle value points are found covering 77.72 percent of the time as returned in PercentGood. This means that the returned average is calculated based on 77.72 percent of the cycle time. Because the same OPCQuality is not found for all the points in the cycle, OPCQuality is set to Doubtful. In the remaining three cycles, only good points occur, all with an OPCQuality of 192.

Because no quality rule is specified in the query using the wwQualityRule parameter, the query uses the default as specified by the QualityRule system parameter. If a quality rule of Extended is specified, any point stored with doubtful OPCQuality will be used to calculate the average, and the point time will therefore be included in the calculation of PercentGood.

SELECT DateTime, TagName, CONVERT(DECIMAL(10, 2), Value) AS Value, OPCQuality, PercentGood FROM History

WHERE TagName = 'ReactTemp'

AND DateTime >= '2005-04-11 11:18:00'

AND DateTime < '2005-04-11 11:23:00'

AND wwRetrievalMode = 'Average'

AND wwCycleCount = 5

AND wwTimeStampRule = 'Start'

The results are:

Query 2

This query demonstrates the use of the average retrieval mode in a wide query. Time-weighted average values are returned for the analog tags ReactTemp and ReactLevel, while regular cyclic points are returned for the discrete tag, WaterValve.

SELECT * FROM OpenQuery(INSQL,

'SELECT DateTime, ReactTemp, ReactLevel, WaterValve FROM WideHistory

WHERE DateTime >= "2004-06-07 08:00"

AND DateTime < "2004-06-07 08:05"

AND wwRetrievalMode = "Average"

AND wwCycleCount = 5

')

The results are: