This section lists sample SQL statements used by j5 Wonderware Historian Interface to retrieve data from Wonderware Historian. A question mark (?) denotes a parameter passed in.
Read current value of tag from the Live table
SELECT t.TagName, l.vValue, l.DateTime, l.Quality
FROM Runtime.dbo.Tag t
INNER REMOTE JOIN Runtime.dbo.Live l ON t.TagName = l.TagName WHERE l.wwTimezone = 'UTC'
AND t.TagName = ?
Current value multiple reads
SELECT t.TagName, l.vValue, l.DateTime, l.Quality
FROM Runtime.dbo.Tag t
INNER REMOTE JOIN Runtime.dbo.Live l ON t.TagName = l.TagName
WHERE l.wwTimezone = 'UTC'
AND t.TagName IN (?,?,?,?)
Read an archived value (at a certain point time) from the History table
SELECT TagName, vValue, DateTime, Quality
FROM Runtime.dbo.History
WHERE wwTimezone = 'UTC'
AND wwRetrievalMode = 'Delta'
AND DateTime = ?
AND TagName = ?
History value multiple reads
SELECT TagName, vValue, DateTime, Quality
FROM Runtime.dbo.History
WHERE wwTimezone = 'UTC'
AND wwRetrievalMode = 'Delta'
AND DateTime = ?
AND TagName IN (?,?,?,?)
Read an archived value between two timestamps from the History table
SELECT TagName, vValue, DateTime, Quality
FROM Runtime.dbo.History
WHERE wwTimezone = 'UTC'
AND wwRetrievalMode='Delta'
AND DateTime >= ?
AND DateTime <= ?
AND TagName = ?
Calculate Maximum Value of Tag from the History table between two timestamps
-
SQL Server Function
SELECT MAX(Value)
FROM Runtime.dbo.History
WHERE wwTimezone = 'UTC'
AND DateTime >= ?
AND DateTime <= ?
AND TagName = ?
-
AnalogSummary History Table
SELECT TagName, OPCQuality as Quality,
Maximum as vValue, StartDateTime as DateTime, EndDateTime as EndDateTime
FROM Runtime.dbo.AnalogSummaryHistory
WHERE wwTimezone = 'UTC'
AND StartDateTime >= ?
AND EndDateTime <=
AND TagName = ?
Calculate Minimum Value of Tag from the History table between two timestamps
-
SQL Server Function
SELECT MIN(Value)
FROM Runtime.dbo.History
WHERE wwTimezone = 'UTC'
AND DateTime >= ?
AND DateTime <= ?
AND TagName = ?
-
AnalogSummary History Table
SELECT TagName, OPCQuality as Quality, Minimum as vValue, StartDateTime as DateTime,
EndDateTime as EndDateTime
FROM Runtime.dbo.AnalogSummaryHistory
WHERE wwTimezone = 'UTC'
AND StartDateTime >= ?
AND EndDateTime <= ?
AND TagName = ?
Calculate Average Value of Tag from the History table between two timestamps
-
SQL Server Function
SELECT AVG(Value)
FROM Runtime.dbo.History
WHERE wwTimezone = 'UTC'
AND DateTime >= ?
AND DateTime <= ?
AND TagName = ?
This is an event-weighted query.
-
Historian Built in Retrieval Mode
SELECT TagName, Quality, vValue, DateTime
FROM Runtime.dbo.History
WHERE wwTimezone = 'UTC'
AND wwRetrievalMode = 'Average'
AND wwTimeStampRule = 'END'
AND wwCycleCount = 1
AND DateTime > ?
AND DateTime <= ?
AND TagName = ?
This is an event-weighted query.
-
AnalogSummary History Table
SELECT TagName, OPCQuality as Quality, Average as vValue, StartDateTime as DateTime,
EndDateTime as EndDateTime
FROM Runtime.dbo.AnalogSummaryHistory
WHERE wwTimezone = 'UTC'
AND StartDateTime >= ?
AND EndDateTime <=
AND TagName = ?
Retrieval Tag Values for use in Chart and Sparklines
-
Retrieve Interpolated Values
SELECT TagName, vValue, DateTime, Quality
FROM Runtime.dbo.History
WHERE wwTimezone = 'UTC' AND wwRetrievalMode ='Cyclic'
AND wwCycleCount = ?
AND DateTime >= ?
AND DateTime <= ?
AND TagName= ?
Where wwCycleCount parameter passed in is the number of sample points configured in the j5 Designer.
-
Best Fit Retrieval
SELECT TagName, vValue, DateTime, Quality
FROM Runtime.dbo.History
WHERE wwTimezone = 'UTC'
AND wwRetrievalMode ='BestFit'
AND wwCycleCount = ?
AND DateTime >= ?
AND DateTime <= ?
AND TagName= ?
Where wwCycleCount parameter passed in is the number of sample points configured in the j5 IndustraForm Designer.
Write Tag Value back to History Table
INSERT Runtime.dbo.v_History (DateTime, wwTimezone, TagName, Value, Quality, wwVersion)
VALUES (?, 'UTC', ?, ?, 192, 'LATEST')