Other Calculation Modes

Calculating the state count of good quality data

This example shows a simple case of counting state transitions. In this example, the value 4 means that a machine is running so we want to count the number of times the tag transitioned from some other value to 4.

Import the following data.

[Tags]
Tagname,DataType,HiEngineeringUnits,LoEngineeringUnits
STATECOUNTTAG,SingleInteger,60,0
STATEBADTAG,SingleInteger,60,0
STATEBADTAG2,SingleInteger,60,0
[Data]
Tagname,TimeStamp,Value,DataQuality
STATECOUNTTAG,06-Aug-2012 8:59:00.000,2,Good
STATECOUNTTAG,06-Aug-2012 9:08:00.000,4,Good
STATECOUNTTAG,06-Aug-2012 9:14:00.000,4,Good
STATECOUNTTAG,06-Aug-2012 9:22:00.000,2,Good
STATEBADTAG,06-Aug-2012 8:59:00.000,2,Good
STATEBADTAG,06-Aug-2012 9:08:00.000,0,Bad
STATEBADTAG,06-Aug-2012 9:14:00.000,2,Good
STATEBADTAG,06-Aug-2012 9:22:00.000,4,Good
STATEBADTAG2,06-Aug-2012 8:59:00.000,2,Good
STATEBADTAG2,06-Aug-2012 9:08:00.000,0,Bad
STATEBADTAG2,06-Aug-2012 9:14:00.000,4,Good
STATEBADTAG2,06-Aug-2012 9:22:00.000,2,Good

Execute the following query in Historian Interactive SQL:

set starttime='08/06/2012 8:00:00',endtime='08/06/2012 10:00:00'
select timestamp,value,quality from ihrawdata where tagname = STATECOUNTTAG and samplingmode=Calculation and 
CalculationMode=StateCount and IntervalMilliseconds=20m and statevalue=4

The following results are returned:

Note that the transition from 2 to 4 (machine started running) happened at 9:08, so it is included in the 9:00 to 9:20 interval.

The data was of bad quality until 8:59:00 which is for 1 minute of the 20 minute interval. The percent good for that interval is 5.

There are two samples with the value 4. We do not count the number of times the statevalue occurred, but the number of transitions from some other value to the state value.

We only count transitions into the state value not out of the state value. So, the transition from 4 to 2 is not counted.

Calculating the state count of bad quality data

Note that this tag had a bad data sample when the collector was restarted. This does not, however, affect the state count.

Run the following query:

set starttime='08/06/2012 8:00:00',endtime='08/06/2012 10:00:00'
select timestamp,value,quality from ihrawdata where tagname = STATEBADTAG and samplingmode=Calculation and 
CalculationMode=StateCount and IntervalMilliseconds=20m and statevalue=4

The following results are returned:

Note that the bad value is ignored and the state change that happened at 9:22 is counted. We do not know if the machine had started and stopped while the collector was shutdown.

If the value did change to running while the collector was shut down then that change is counted as in shown in the following example:

set starttime='08/06/2012 8:00:00',endtime='08/06/2012 10:00:00'
select timestamp,value,quality from ihrawdata where tagname = STATEBADTAG2 and samplingmode=Calculation and 
CalculationMode=StateCount and IntervalMilliseconds=20m and statevalue=4

Calculating the state count of enumerated set data

When querying a tag that uses enumerated sets, use the string state name as the state value.

Using the data from previous example, assume that the STATECOUNTTAG had an enumerated set with the values as 2=Stopped and 4=Running.

You should use this query with statevalue of Running instead of the native value 4.

set starttime='08/06/2012 8:00:00',endtime='08/06/2012 10:00:00'
select timestamp,value,quality from ihrawdata where tagname = STATECOUNTTAG and samplingmode=Calculation and 
CalculationMode=StateCount and IntervalMilliseconds=20m and statevalue='Running'

The results match with the results when statevalue=4 is used.

Calculating the state time of good quality data

set starttime='08/06/2012 8:00:00',endtime='08/06/2012 10:00:00'
select timestamp,value,quality from ihrawdata where tagname = STATECOUNTTAG and samplingmode=Calculation and 
CalculationMode=StateTime and IntervalMilliseconds=20m and statevalue=4

A 20 minute interval is 20*60*1000=1200000 milliseconds. In the 9:00 to 9:20 interval the value was in state 4 from 9:08 to 9:20 which is 12 minutes * 60 *1000 = 720000 milliseconds.

In the 9:20 to 9:40 interval the value was in state 4 from 9:20 to 9:22 which is 2*60*1000 = 120000 milliseconds.

Calculating the state time of bad quality data

This tag has a bad data sample such as when the collector was restarted. A new value is recorded when the collector is started.

set starttime='08/06/2012 8:00:00',endtime='08/06/2012 10:00:00'
select timestamp,value,quality from ihrawdata where tagname = STATEBADTAG2 and samplingmode=Calculation and 
CalculationMode=StateTime and IntervalMilliseconds=20m and statevalue=4

Tagname,TimeStamp,Value,DataQuality
STATEBADTAG2,06-Aug-2012 8:59:00.000,2,Good
STATEBADTAG2,06-Aug-2012 9:08:00.000,0,Bad
STATEBADTAG2,06-Aug-2012 9:14:00.000,4,Good
STATEBADTAG2,06-Aug-2012 9:22:00.000,2,Good

The following results are returned:

In the interval between 9:00 to 9:20, the value was in state 4 from 9:14 to 9:20 = 6 minutes * 60 * 1000 = 360000 milliseconds.

In the interval between 9:20 to 9:40, the value was in state 4 from 9:20 to 9:22 = 2 minutes * 60 * 1000 = 120000 milliseconds.

Calculating the OPCQOR

The following is the data set used to run the query on.

TagName,Timestamp,Value
DATA1:Bad-0 OPC-(60)(08/09/12 18:00:01.000,Val=10
DATA2:Bad-0 OPC-(59)(08/09/12 18:00:02.000,Val=10
DATA3:Bad-0 OPC-(58)(08/09/12 18:00:03.000),Val=10
DATA4:Bad-0 OPC-(57)(08/09/12 18:00:04.000),Val=10
DATA5:Bad-0 OPC-(56)(08/09/12 18:00:05.000),Val=10
DATA6:Bad-0 OPC-(55)(08/09/12 18:00:06.000),Val=10
DATA7:Bad-0 OPC-(54)(08/09/12 18:00:07.000),Val=10
DATA8:Bad-0 OPC-(53)(08/09/12 18:00:08.000),Val=10
DATA9:Bad-0 OPC-(52)(08/09/12 18:00:09.000),Val=10
DATA10:Bad-0 OPC-(51)(08/09/12 18:00:10.000),Val=10
DATA11:Bad-0 OPC-(50)(08/09/12 18:00:11.000),Val=10

The following query retrieves the OPCQOR data with a start time of 18:00:00 and end time of 18:00:10 with a 2 second time interval.

set starttime='08/09/2012 18:00:00',endtime='08/09/2012 18:00:10'
select tagname,timestamp,value,Quality from ihrawdata where tagname like OPCQualityDataTag and samplingmode=Calculated
and calculationmode=OPCQOR and IntervalMilliseconds=2S

The following output is retrieved.

Calculating the OPCQAND

The following query retrieves the OPCQAND data with a start time of 18:00:00 and end time of 18:00:10 with a 2 second time interval.

set starttime='08/09/2012 18:00:00',endtime='08/09/2012 18:00:10'
select tagname,timestamp,value,Quality,opcquality from ihrawdata where tagname like OPCQualityDataTag and 
samplingmode=Calculated and calculationmode=OPCQAND and IntervalMilliseconds=2S

he following output is retrieved.

Using TagStats Calculation Mode

This image displays the TagStats calculation mode example in the Proficy Historian Interactive SQL Application.

In this example we perform the calculations for a single interval by giving numberofsamples=1.