In this section, you will use the Microsoft SQL Server Management Studio to retrieve simple replicated data from a remote (Tier-2) historian, and summarized data from a Tier-2 historian or the local (Tier-1) historian using SQL queries.

Simple Replication Data in SQL Server
Simple replication is only possible on a remote Tier-2 Historian and the replicated values are stored in the regular history table. The simple replicated tags can be easily identified by their default naming scheme:

<ReplicationDefaultPrefix>.<SourceTagName>

Where the ReplicationDefaultPrefix is the name of the Tier-1 Historian where the original tag exists

In the following example, the query will retrieve the values of the tag SteamValve.PV simple replicated from the Tier-1 machine called HistorianA. Assume you are running the query directly in the Tier-2 machine.

Select DateTime, TagName, Value From History
Where
TagName = 'HistorianA.SteamValve.PV'
AND    DateTime > Dateadd(mi, -30, Getdate()) AND DateTime <= Getdate()

 
Summary Replication Data in SQL Server

Depending on what type of summary replication was configured, analog or state, data is stored in two tables on the Historian Server database.

• AnalogSummaryHistory view
• StateSummaryHistory view

AnalogSummaryHistory view

The AnalogSummaryHistory view is a wide view that returns multiple statistics results for analog summary tags within a single query.

The following are the columns found in the AnalogSummaryHistory view table that contain the statistical information for the analog summary tags. (For detailed information on all the columns in the table, see the product documentation.)

• PercentGood – Time in seconds that the value was good for the retrieval cycle (pro-rated for partial cycles)
• First – If at least one non-NULL point exists for the tag in question within the retrieval cycle, then the value returned is the first point stored with a time stamp within the retrieval cycle
• FirstDateTime – Timestamp associated with first value

• Last – If at least one non-NULL point exists for the tag in question within the retrieval cycle, then the value returned is the last point stored with a time stamp within the retrieval cycle
• LastDateTime – Timestamp associated with last value
• Minimum – If at least one non-NULL point exists for the tag in question within the retrieval cycle, then the value returned is the minimum point stored with a time stamp within the retrieval cycle
• MinDateTime – Timestamp associated with Min value
• Maximum – If at least one non-NULL point exists for the tag in question within the retrieval cycle, then the value returned is the maximum point stored with a time stamp within the retrieval cycle
• MaxDateTime – Timestamp associated with Max value
• vergeAa – Time weighted average value of retrieval cycle
• StdDev – Time weighted standard deviation value of the retrieval cycle
• Integral – Area under value curve of retrieval cycle
• ValueCount – Number of values in a particular cycle

For example, the following query returns the minimum, maximum, and average values for the SysTimeSec tag for the last two cycles of 30 minutes.

SELECT TagName, Minimum, Maximum, Average FROM AnalogSummaryHistory
WHERE TagName = 'SysTimeSec'
AND
StartDateTime >= dateadd(mi,-60, Getdate())
AND EndDateTime <    Getdate()
AND wwCycleCount = 2
 

StateSummaryHistory view

(INSQL.Runtime.dbo.StateSummaryHistory)

The StateSummaryHistory extension table returns results for state summary points.
The following are the columns found in the StateSummaryHistory view table that contain the statistical information for the discrete summary tags. 

• StateCount – Number of times the state occurred within the retrieval cycle, including states that only partially occur in the cycle
• ContainedStateCount – Number of times the state occurred fully contained within the retrieval cycle. States that only partially occur in the cycle are not counted.
• StateTimeMin – Minimum time in this state among all occurrences of this state during this retrieval cycle, including state occurrences that fall only partially within the period. An occurrence that was partially contained in two or more consecutive storage cycles is converted to a contained state within the retrieval cycle if possible.
• StateTimeMinContained – Minimum of the contained times in this state among all occurrences of this state during the entire retrieval cycle, excluding state occurrences that fall only partially within the period. An occurrence that was partially contained in two or more consecutive storage cycles is converted to a contained state within the retrieval cycle if possible.

• StateTimeMax – Maximum time in this state among all occurrences of this state during this retrieval cycle, including state occurrences that fall only partially within the period. An occurrence that was partially contained in two or more consecutive storage cycles is converted to a contained state within the retrieval cycle if possible.
• StateTimeMaxContained – Maximum of the contained times in this state among all occurrences of this state during the entire retrieval cycle, excluding state occurrences that fall only partially within the period. An occurrence that was partially contained in two or more consecutive storage cycles is converted to a contained state within the retrieval cycle if possible.
• StateTimeAvg – Average time in this state among all occurrences of this state during this retrieval cycle, including state occurrences that fall only partially within the period.
• StateTimeAvgContained – Average time in this state among all occurrences of this state during this retrieval cycle, excluding state occurrences that fall only partially within the period. An occurrence that was partially contained in two or more consecutive storage cycles is converted to a contained state within the retrieval cycle if possible.
• StateTimeTotal – Total time in this state during this retrieval cycle, including state occurrences that fall only partially within the period
• StateTimeTotalContained – Total time in this state during this retrieval cycle, excluding state occurrences that fall only partially within the period. An occurrence that was partially contained in two or more consecutive storage cycles is converted to a contained state within the retrieval cycle if possible.
• StateTimePercent – Percent of the time during this retrieval cycle that the tag was in this state, including state occurrences that fall only partially within the period
• StateTimePercentContained – Percentage of the entire retrieval cycle time that the tag was in this state, excluding state occurrences that fall only partially within the period. This is a ratio between StateTimeTotalContained and StateTimeTotal expressed as a percentage in the range 0 to 100. An occurrence that was partially contained in two or more consecutive storage cycles is converted to a contained state within the retrieval cycle if possible.

For example, 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 two cycles of 30 minutes. One row is returned for each state.

SELECT TagName, Value, StateCount, StateTimeTotal, StateTimePercent FROM
StateSummaryHistory
WHERE TagName = 'SysPulse'
AND
StartDateTime >= dateadd(minute,-60,Getdate()) AND EndDateTime <
Getdate()
AND wwCycleCount = 2