Use SliceBy

You can retrieve summary statistics for a tag per batch by using the SliceBy parameter in a query. Batches can be defined by changes in a different tag, such as a batch ID or valve position.

For example, suppose you wanted statistics on flow rate depending on the position of a valve. Each time the valve position changes, a new batch is reported. Here is a query to retrieve that information:

select SliceByValue, TagName, StartDateTime, EndDateTime, OPCQuality, PercentGood, wwResolution, Average

from AnalogSummaryHistory

where TagName='M31.FlowIn'

and SliceBy='M31.ValveIn'

and StartDateTime>='2018-11-27 0:00'

and EndDateTime<='2018-11-28 0:00'

The results are:

This query uses the same data. This time, using SliceByValue, the query retrieves statistics on the flow rate, but only for those batches when the valve is open.

select SliceByValue, TagName, StartDateTime, EndDateTime, OPCQuality, PercentGood, wwResolution, Average

from AnalogSummaryHistory

where TagName='M31.FlowIn'

and SliceBy='M31.ValveIn'

and SliceByValue=1

and StartDateTime>='2018-11-27 0:00'

and EndDateTime<='2018-11-28 0:00'

The results this time are:

select SliceByValue, TagName, StartDateTime, EndDateTime, OPCQuality, PercentGood, wwResolution, Average

from AnalogSummaryHistory

where TagName='M31.FlowIn'

and SliceBy='M31.ValveIn'

and SliceByValue=1

and EndDateTime>='2018-11-27 0:00'

and StartDateTime<='2018-11-28 0:00'

The results are:

SliceBy Queries That Include Future

If you define a timeframe that includes a batch that is not completed, you will get an summary statictics for the batch data currently available, but it will be marked with "OPCQuality=64", meaning the quality is uncertain because the end time is unknown. An ending batch can be incomplete because:

• The batch is currently in progress and the next batch has not yet initiated. Batches are defined by changes in a different tag. If that tag has not yet changed, the current batch is in progress and the next batch has not initiated.

• The batch did physically completed, but the data hasn’t yet been written to the server due to latency, power-outage, etc.

Here is an example. This query retrieves average values for batches that completed on Nov 28. The "select getdate()" line shows that the current timestamp for the query is Nov 28, 2018 at 4:31. Since the day is not over, the system cannot know for sure that another batch won’t be initiated. Therefore, the last retrieved batch is incomplete.

select SliceByValue, TagName, StartDateTime, EndDateTime, OPCQuality, PercentGood, wwResolution, Average

from AnalogSummaryHistory

where TagName='M31.Level'

and SliceBy='M31.Batch'

and EndDateTime>='2018-11-28 0:00'

and StartDateTime<='2018-11-29 0:00'

select getdate()

The results are:

Using Group By with SliceBy

You can use Group By in a query to get an overall summary off all occurrences of a state. For example, you might want an overall summary of production when a piece of equipment was in a certain position.

Here is an example of using Group By to organize data by tag state. This query retrieves an overall summary of statistics for each position of the valve. One line summarizes all batches for the day where the valve was closed (0) and another line for all batches with the valve open (1).

select SliceByValue, TagName, StartDateTime=min(StartDateTime), EndDateTime=max(EndDateTime), TotalTime=sum(wwResolution), Maxiumum=max(Maximum), Total=sum(Integral), Average=sum(Average*wwResolution)/sum(wwResolution), AvgOfAvg=avg(Average)

from AnalogSummaryHistory

where TagName='M31.FlowIn'

and SliceBy='M31.ValveIn'

and EndDateTime>='2018-11-27 0:00'

and StartDateTime<='2018-11-28 0:00'

group by TagName, SliceByValue

The results are:

Note: This example correctly calculates the overall average for each state in the "Average" column by weighting the duration of each state. As explained by Simpson’s Paradox, the simpler, "AvgOfAvg" calculation is not statistically accurate and can differ significantly with some data sets.