Lab 3 – Query Examples 3-31
FROM History
WHERE Tagname = 'TT_001.PV'
AND Datetime > 'January 1, 1970 10:00'
AND Datetime <= Getdate()
-- Example 12
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Using Dateadd with Getdate():
-- Using Dateadd in conjunction with the Getdate() function
-- allows the user to specify an standard offset from the
-- current time. The following query will always return
-- the last hour of data for the tags 'TT_001.PV’ and ‘LIT_001.PV':
SELECT Datetime, Tagname, vValue
FROM History
WHERE Tagname IN ('TT_001.PV', 'LIT_001.PV')
AND Datetime > Dateadd(mi, -60, Getdate())
AND Datetime <= Getdate()
-- Example 13
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Accessing Data From WideHistory
-- Wide Tables:
-- In the preceding example the data is hard to read. This is
-- because the data for a particular tag is not grouped for
-- easy viewing. A special set of tables are provided which
-- use the tagname as a column name, thus making the table
-- easier to view. To access these tables an OpenQuery must
-- be used:
Wonderware System Platform Course - Part 2
3-32 Module 3 – History Data Storage and Retrieval
SELECT *
FROM OpenQuery (INSQL, '
SELECT Datetime, [LIT_001.PV], [InletValve_001.PV.IsPassive],
SysString
FROM WideHistory
WHERE Datetime > Dateadd(mi, -65, Getdate())
AND Datetime <= Getdate()')
-- Notice the square brackets around LIT_001.PV and InletValve_001.PV.IsPassive
-- This is needed because the dot is a reserved character in MS SQL Server.
-- In the results of the preceding query, notice that a value is given for
-- SysString even though it only changed at the top of the hour, When using
-- delta queries on a wide table, a new row is returned if any of the requested
-- tags change value.
-- Example 14
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Using 'wwRetrievalMode', Cyclic vs. Delta Retrieval
-- Process data users use data differently than most other users of databases
-- (banks, scientific communities etc.). To allow our users to view the data in
-- the most advantagious way, a number of special functions were added to the
-- Transact-SQL language. We call these functions RealTime SQL. The first we
-- will explore is the wwRetrievalMode function. wwRetrievalMode allows the user
-- to override the default retrieval mode for the history tables on a per query
-- basis.
-- First is the concept of "Cyclic" versus "Delta" retrieval modes. The History
-- table returns data using Delta retrieval mode as default. This means that a
-- new row is returned only if the value changes. Notice that the interval
-- between each row is different for the following queries:
SELECT Datetime, Tagname, vValue
FROM History
Wonderware Training
Lab 3 – Query Examples 3-33
WHERE Tagname = 'TT_001.PV'
AND Datetime > Dateadd(mi, -100, Getdate())
AND Datetime <= Getdate()
-- wwRetrievalMode allows you to override the default retrieval mode.
-- In the following query the retrieval mode is forced to cyclic for
-- this one query only. Notice the returned values are evenly spaced every
-- minute.
SELECT Datetime, Tagname, vValue
FROM History
WHERE Tagname = 'TT_001.PV'
AND wwRetrievalMode = 'cyclic'
AND Datetime > Dateadd(mi, -100, Getdate())
AND Datetime <= Getdate()
-- Delta retrieval mode can also be explicitly indicated:
SELECT Datetime, Tagname, vValue
FROM History
WHERE Tagname = 'TT_001.PV'
AND wwRetrievalMode = 'delta'
AND Datetime > Dateadd(mi, -100, Getdate())
AND Datetime <= Getdate()
-- Cyclic retrieval is useful when looking for profiles and averages, while
-- Delta retrieval is useful when looking for alterations in data, like a
-- setpoint change that occured once during a shift.
-- Example 15
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Wonderware System Platform Course - Part 2
3-34 Module 3 – History Data Storage and Retrieval
-- Using 'Full' Retrieval Mode
-- For full retrieval mode, all stored values will be returned, regardless
-- of whether a value or quality has changed since the last value. If used in
-- conjunction with storage without filtering (no delta or cyclic storage mode
-- applied), the full retrieval mode allows for the retrieval of all values and
-- quality that originated from the plant floor data source or from another
-- application.
SELECT Datetime, Tagname, vValue
FROM History
WHERE Tagname = 'LIT_001.PV'
AND wwRetrievalMode = 'full'
AND Datetime > Dateadd(mi, -50, Getdate())
AND Datetime <= Getdate()
-- Full retrieval mode is especially important when used to retrieve data
-- stored by Wonderware Application Server, since it will display every single
-- value coming from the Galaxy.
-- Example 16
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Using 'wwResolution'
-- Let's examine a simple cyclic query:
SELECT Datetime, Tagname, vValue
FROM History
WHERE Tagname = 'TT_001.PV'
AND wwRetrievalMode = 'cyclic'
AND Datetime > Dateadd(mi, -100, Getdate())
AND Datetime <= Getdate()
Wonderware Training
Lab 3 – Query Examples 3-35
-- Notice that Historian returns 100 rows evenly spaced over the
-- time period specified. This is the default behaviour, but there are
-- mechanisms to specify 'how much' data you want.
-- wwResolution tells Historian to return data separated by a fixed
-- time interval (set in milliseconds). This works best with queries in CYCLIC
-- retrieval mode. The following query returns data every second for the last
-- 10 minutes.
SELECT Datetime, Tagname, vValue
FROM History
WHERE Tagname = 'TT_001.PV'
AND wwRetrievalMode = 'cyclic'
AND Datetime > Dateadd(mi, -10, Getdate())
AND Datetime <= Getdate()
AND wwResolution = 1000
-- By changing the wwresolution to 60000 we get a record every minute:
SELECT Datetime, Tagname, vValue
FROM History
WHERE Tagname = 'TT_001.PV'
AND wwRetrievalMode = 'cyclic'
AND Datetime > Dateadd(mi, -10, Getdate())
AND Datetime <= Getdate()
AND wwResolution = 60000
-- Example 17
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- The following example uses cyclic retrieval mode and wwResolution
-- to create a wide format report for the last hour:
Wonderware System Platform Course - Part 2
3-36 Module 3 – History Data Storage and Retrieval
SELECT *
FROM OpenQuery(InSQL, 'SELECT Datetime, [LIT_001.PV], [TT_001.PV],
[InletValve_001.PV.IsActive1]
FROM WideHistory
WHERE Datetime > Dateadd(hh, -1, Getdate())
AND Datetime <= Getdate()
AND wwRetrievalMode = "Cyclic"
AND wwResolution = 60000')
-- Example 18
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Using 'wwCycleCount'
-- Another way to control the amount of data returned from a query is
-- the wwCycleCount function. Using wwCycleCount, the number of rows that will
-- be returned is specified. When wwCycleCount is used in conjunction with
-- CYCLIC retrieval queries the rows returned are evenly spaced thoughout
-- the time period specified. However, when used in a delta query, wwCycleCount
-- returns the a row each time the value changes, up to the number of rows
-- specified.
-- First in CYCLIC mode:
SELECT Datetime, Tagname, vValue
FROM History
WHERE Tagname = 'SysTimeSec'
AND wwRetrievalMode = 'cyclic'
AND Datetime > Dateadd(mi, -10, Getdate())
AND Datetime <= Getdate()
AND wwCycleCount = 25
-- Now let's see what delta does:
SELECT Datetime, Tagname, vValue
Wonderware Training
Lab 3 – Query Examples 3-37
FROM History
WHERE Tagname = 'SysTimeSec'
AND wwRetrievalMode = 'delta'
AND Datetime > Dateadd(mi, -10, Getdate())
AND Datetime <= Getdate()
AND wwCycleCount = 25
-- Setting wwCycleCount to 'zero' causes ALL values within the time
-- period to be returned:
SELECT Datetime, Tagname, vValue
FROM History
WHERE Tagname = 'SysTimeSec'
AND wwRetrievalMode = 'delta'
AND Datetime > Dateadd(mi, -10, Getdate())
AND Datetime <= Getdate()
AND wwCycleCount = 0
-- This is useful when trying to retrieve data when the sample rate
-- is unknown.
-- Example 19
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Using Time-Weighted Average Retrieval Mode
-- To get an average calculation from history data, we need to decide how much
-- data goes into the query, allowing us to decide how much work the InSQL
-- Server computer performs. The following is a rough estimate of an hourly
-- average using one sample every minute:
SELECT AVG(Value)
FROM History
WHERE Tagname = 'TT_001.PV'
AND Datetime > Dateadd(hh, -1, Getdate())
AND Datetime <= Getdate()
Wonderware System Platform Course - Part 2
3-38 Module 3 – History Data Storage and Retrieval
AND wwRetrievalMode = 'delta'
AND wwResolution = 60000
-- The SQL Server AVG aggregate is a simple statistical average. The result will
-- depend on the resolution of samples. Using the time weighted average (TWA)
-- retrieval mode will use actual values in the table to calculate the average.
-- Even if the SQL Server AVG aggregate returns a similar value, the data is
-- returned much faster. The following example will return the average for the
-- last hour:
SELECT Datetime, Tagname, vValue
FROM History
WHERE Tagname = 'TT_001.PV'
AND Datetime > Dateadd(hh, -1, Getdate())
AND Datetime <= Getdate()
AND wwRetrievalMode = 'average'
AND wwCycleCount = 1
-- Example 20
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Time Weighted Average (TWA) retrieval mode is a true cyclic mode, so you can
-- divide the entire period of time into several periods. The historian will
-- return one row for each tag in the query for each cycle. To specify how many
-- cycles you want, you can use wwResolution or wwCycleCount.
-- The following query will return an hourly time weighted average for the last
-- shift:
SELECT Datetime, Tagname, vValue, PercentGood
FROM History
WHERE Tagname = 'TT_001.PV'
AND Datetime > Dateadd(hh, -8, Getdate())
AND Datetime <= Getdate()
AND wwRetrievalMode = 'average'
AND wwResolution = 3600000
Wonderware Training
Lab 3 – Query Examples 3-39
-- Example 21
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Time-in-State Retrieval Mode
-- The time-in-state retrieval mode returns the amount of time that a tag has
-- been in a particular state for each retrieval cycle. This retrieval mode is
-- useful for determining how long a machine has been running or stopped, how
-- long a valve has been opened or closed, and so on:
SELECT Datetime, Tagname, vValue, StateTime, wwStateCalc, qualitydetail
FROM History
WHERE Tagname = 'InletValve_001.PV.IsActive1'
AND Datetime > Dateadd(mi, -60, Getdate())
AND Datetime <= Getdate()
AND wwRetrievalMode = 'ValueState'
AND wwCycleCount = 2
-- The values can also be retrieved as percentage
SELECT Datetime, Tagname, vValue, StateTime, wwStateCalc
FROM History
WHERE Tagname = 'InletValve_001.PV.IsActive1'
AND Datetime > Dateadd(mi, -60, Getdate())
AND Datetime <= Getdate()
AND wwRetrievalMode = 'ValueState'
AND wwStateCalc = 'Percent'
AND wwCycleCount = 2
-- The wwStateCalc specifies the type of state calculations (aggregations) to be
-- performed on the data. Valid values are 'Total', 'Percent', 'Minimum',
-- 'Maximum' and 'Average', 'Total' being the calculation by default.
-- The following query will return an hourly report of the average time the
-- Steam Valve has remained opened for the last shift:
Wonderware System Platform Course - Part 2
3-40 Module 3 – History Data Storage and Retrieval
SELECT Datetime, Tagname, vValue, StateTime, wwStateCalc
FROM History
WHERE Tagname = 'InletValve_001.PV.IsActive1'
AND Datetime > Dateadd(hh, -8, Getdate())
AND Datetime <= Getdate()
AND vValue = 1
AND wwRetrievalMode = 'ValueState'
AND wwResolution = 3600000
-- Example 22
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Using 'Best Fit' Retrieval Mode
-- For the "best fit" retrieval mode, the total time for the query is divided
-- into even sub-periods, and then up to five values are returned for each
-- sub-period: first, last, minimum, maximum and first 'exception' values. This
-- mode allows for a compromise between delta retrieval mode and cyclic
-- retrieval mode:
SELECT Datetime, Tagname, vValue
FROM History
WHERE Tagname = 'LIT_001.PV'
AND Datetime > Dateadd(mi, -10, Getdate())
AND Datetime <= Getdate()
AND wwRetrievalMode = 'BestFit'
AND wwResolution = 30000
-- Observe the amount of values returned for the above query, compared to the
-- values returned for a delta retrieval:
SELECT Datetime, Tagname, vValue
FROM History
WHERE Tagname = 'LIT_001.PV'
AND Datetime > Dateadd(mi, -10, Getdate())
AND Datetime <= Getdate()
Wonderware Training
