wwtools/histdb/05-query-recipes.md

Query recipes

Worked SQL patterns for the questions that come up most often. Each recipe links back to the deeper concept doc when more context is needed.

Querying the History table — vanilla

SELECT DateTime, Sec = DATEPART(ss, DateTime), TagName, Value
FROM History
WHERE TagName = 'ReactLevel'
  AND DateTime >= '2001-03-13 13:15:00'
  AND DateTime <= '2001-03-13 14:15:00'
  AND wwRetrievalMode = 'Cyclic';   -- explicit, even though Cyclic is the analog default

Without wwCycleCount / wwResolution, default is 100 rows. See 03-retrieval-modes.md.

Latest snapshot per tag — Live

SELECT TagName, Value
FROM Live
WHERE TagName = 'ReactLevel';

Live is fed by streamed updates only. Non-streamed paths bypass it (CSV import, store-and-forward, post-shutdown writes, "Latest" revision data) — query History if Live shows stale results.

Wide tables — WideHistory via OPENQUERY

SELECT * FROM OPENQUERY(INSQL, '
  SELECT DateTime, ReactLevel, ReactTemp
  FROM WideHistory
  WHERE DateTime >= "2001-03-02 06:20:00"
    AND DateTime <= "2001-03-02 06:30:00"
    AND wwRetrievalMode = "Cyclic"
    AND wwResolution    = 60000
');

Column types follow tag types. Up to 1024 columns. Variables can't appear inside the body; if you need parameterization, build the body string in T-SQL and EXEC it.

Wide tables in Delta mode

Delta over a wide table is well-defined for a single tag. With multiple tags, what's a "delta" — the OR of all tags changed? — is ambiguous. Stick to Cyclic / Interpolated for multi-tag wide queries unless the source PDF section "Querying Wide Tables in Delta Retrieval Mode" (p. 131) covers your exact case.

Joining a SQL Server table to an extension table — INNER REMOTE JOIN

-- "Give me history for every string-tag with MaxLength = 64"
SELECT DateTime, T.TagName, vValue, Quality, QualityDetail
FROM StringTag T
INNER REMOTE JOIN History H ON T.TagName = H.TagName
WHERE T.MaxLength = 64
  AND DateTime >= '2002-03-10 12:00:00.000'
  AND DateTime <= '2002-03-10 16:40:00.000'
  AND wwRetrievalMode = 'Delta';

Pattern: driver SQL Server table on the left, extension table on the right. Without INNER REMOTE JOIN, SQL Server pulls the entire extension table back into the engine before joining — order of magnitude slower.

Aggregates inside OPENQUERY

MIN, MAX, AVG, SUM are supported inside the body; STDEV is not.

SELECT * FROM OPENQUERY(INSQL, '
  SELECT
    "Minimum" = MIN(ReactLevel),
    "Maximum" = MAX(ReactLevel),
    "Average" = AVG(ReactLevel),
    "Sum"     = SUM(ReactLevel)
  FROM WideHistory
  WHERE DateTime > "2001-02-28 18:55:00"
    AND DateTime < "2001-02-28 19:00:00"
    AND wwRetrievalMode = "Cyclic"
');

For STDEV or other unsupported aggregates, run the inner OPENQUERY to fetch raw rows and apply the aggregate outside.

COUNT(*) quirks

COUNT(*) works directly in four-part queries:

SELECT COUNT(*)
FROM History
WHERE TagName = 'SysTimeSec'
  AND DateTime >= '2001-12-20 00:00'
  AND DateTime <= '2001-12-20 00:05'
  AND wwRetrievalMode = 'delta'
  AND Value >= 30;

Inside OPENQUERY, you cannot apply arithmetic to COUNT(*). Push the count into the inner query and do math outside:

SELECT COUNT(*), COUNT(*)/2 FROM OPENQUERY(INSQL, '
  SELECT DateTime, vValue, Quality, QualityDetail
  FROM History
  WHERE TagName IN ("SysTimeSec")
    AND DateTime >= "2002-04-16 03:00:00.000"
    AND DateTime <= "2002-04-16 06:00:00.000"
    AND wwRetrievalMode = "Delta"
');

Arithmetic across columns

In OPENQUERY, math operators need spaces around them:

SELECT * FROM OPENQUERY(INSQL, '
  SELECT DateTime, ReactLevel, Prodlevel, Sum = ReactLevel + Prodlevel
  FROM WideHistory
  WHERE DateTime > "2001-02-28 18:55:00"
    AND DateTime < "2001-02-28 19:00:00"
    AND wwRetrievalMode = "Cyclic"
');

ReactLevel+Prodlevel (no spaces) parses as a column-name suffix and breaks. ReactLevel + Prodlevel works.

GROUP BY outside, raw rows inside

GROUP BY doesn't work inside OPENQUERY. Wrap and group outside:

SELECT TagName,
       AvgValue = AVG(CAST(Value AS FLOAT))
FROM OPENQUERY(INSQL, '
  SELECT TagName, Value FROM History
  WHERE TagName IN ("SysTimeSec","SysTimeMin")
    AND DateTime >= "2024-01-01 00:00"
    AND DateTime <= "2024-01-01 01:00"
    AND wwRetrievalMode = "Delta"
') x
GROUP BY TagName;

Variant vValue filtering

Always cast — see 02-syntax-limits.md:

SELECT DateTime, vValue
FROM OPENQUERY(INSQL, '
  SELECT DateTime, vValue
  FROM History
  WHERE TagName IN ("SomeFloatTag")
    AND DateTime >= "2024-01-01 00:00"
    AND DateTime <= "2024-01-01 01:00"
    AND wwRetrievalMode = "Delta"
') x
WHERE CONVERT(float, vValue) = 20.0;

Time-zone-aware queries

wwTimeZone re-projects timestamps into the named zone. SQL Server still does the datetime math in server local time before passing the predicates down — so DATEADD(...) works in server local, then results come back in the requested zone.

SELECT DateTime, TagName, Value
FROM History
WHERE TagName IN ('SysTimeHour','SysTimeMin','SysTimeSec')
  AND DateTime > DATEADD(mi, -30, GETDATE())
  AND wwTimeZone = 'Eastern Daylight Time';

Time between value changes (wwResolution on output)

In Cyclic, Delta, Full, the wwResolution virtual column on output reports milliseconds since the previous row for the same tag. -1 means the gap exceeded ~25 days (> 2,147,000,000 ms). Doesn't apply to AnalogSummaryHistory / StateSummaryHistory.

SELECT DateTime, Value, wwResolution
FROM History
WHERE TagName = 'PumpRunning'
  AND DateTime >= '2024-01-01 00:00'
  AND DateTime <  '2024-01-02 00:00'
  AND wwRetrievalMode = 'Delta';

Total time the value matched a condition — sum the resolutions:

SELECT SUM(wwResolution) AS TotalMs
FROM OPENQUERY(INSQL, '
  SELECT DateTime, Total = Pump1 + Pump2, wwResolution
  FROM WideHistory
  WHERE DateTime >= "2012-03-08 16:00"
    AND DateTime <  "2012-03-08 17:00"
    AND wwRetrievalMode = "DELTA"
') x
WHERE Total = 0;

SELECT INTO from history

Materializing a slice into a normal SQL Server table is supported via OPENQUERY:

DROP TABLE IF EXISTS dbo.MyTable;

SELECT DateTime,
       Sec = DATEPART(ss, DateTime),
       mS  = DATEPART(ms, DateTime),
       ReactTemp, ReactLevel
INTO dbo.MyTable
FROM OPENQUERY(INSQL, '
  SELECT DateTime, ReactTemp, ReactLevel
  FROM WideHistory
  WHERE DateTime >= "2001-03-02 06:00"
    AND DateTime <= "2001-03-02 07:00"
    AND wwRetrievalMode = "Cyclic"
    AND wwResolution    = 60000
');

SliceBy (analog summary tables)

AnalogSummaryHistory supports a SliceBy column that buckets results by the value of another tag at the same time. Useful for "summarize flow when a valve was in each state":

SELECT SliceByValue, TagName,
       StartDateTime = MIN(StartDateTime),
       EndDateTime   = MAX(EndDateTime),
       TotalTime     = SUM(wwResolution),
       Maximum       = 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 00:00'
  AND StartDateTime <= '2018-11-28 00:00'
GROUP BY TagName, SliceByValue;

Annotations

Free-text notes attached to a tag at a moment. Insert, then query:

DECLARE @UserKey INT;
SELECT @UserKey = UserKey FROM UserDetail WHERE UserName = 'wwAdmin';

INSERT INTO Annotation (TagName, UserKey, DateTime, Content)
VALUES ('ReactLevel', @UserKey, GETDATE(), 'The pump is off');

SELECT DateTime, TagName, Content
FROM Annotation
WHERE TagName = 'ReactLevel'
  AND DateTime > '2024-02-27'
  AND DateTime <= GETDATE();

Bitwise extraction from a packed integer tag

When multiple discrete signals are packed into one PLC register and that register is mapped to a single Historian tag, use bitwise SQL:

SELECT
  CONVERT(BIT, CAST(Value AS INT) & 1)  AS Bit0,
  CONVERT(BIT, CAST(Value AS INT) & 2)  AS Bit1,
  CONVERT(BIT, CAST(Value AS INT) & 4)  AS Bit2,
  CONVERT(BIT, CAST(Value AS INT) & 8)  AS Bit3
FROM dbo.History
WHERE TagName = 'PackedDigitals'
  AND DateTime >= '2024-01-01 00:00'
  AND DateTime <  '2024-01-01 01:00'
  AND wwRetrievalMode = 'Delta';

CAST(... AS INT) caps the addressable bits at 32. Add 2^(bit-1) for each subsequent bit.

Comparison operators with Delta

The boundary semantics matter. With >= on the start, an initial value is synthesized at the start time. With >, no initial value is returned. Same for < / <= on the end. See PDF pp. 149-153 for the full grid.

-- "Anything strictly within the window, no boundary synthesis"
WHERE DateTime > '2024-01-01 00:00'
  AND DateTime < '2024-01-02 00:00'
  AND wwRetrievalMode = 'Delta';

Retrieval across a data gap

When a tag has a gap (I/O server disconnect, undeployed object), retrieval inserts well-defined boundary points:

Boundary	Value	Quality	QualityDetail
Last point before gap (closing point)	last good value	varies	varies
First point after gap (initial of next block)	snapshot	0	96 (0x60 = 150 decimal in some prints)
Cyclic boundary inside the gap	NULL	256 (0x100)	0

Quality / value-deadband behavior across gaps: a value deadband is not applied across a NULL — every value immediately after a NULL is returned. A time deadband still suppresses values that fall within its window even across a gap.

Mixing wwCycleCount, wwResolution, and wwRetrievalMode

Set behavior table (PDF p. 142):

Mode	wwResolution	wwCycleCount	Result
Cyclic	N	0 / unset	Apply N-ms resolution to all stored data in window.
Cyclic	0 / unset	0	Server returns 100,000 rows per tag.
Cyclic	0 / unset	N	N evenly-spaced rows.
Cyclic	N	(any — ignored)	N-ms resolution wins; cycle count ignored.
Cyclic	(none)	(none / negative)	100 rows per tag (default).
Delta / Full	(any — ignored)	N	First N matching rows.
Delta / Full	(any — ignored)	(none)	All matching rows in the window.

Bounding-value behavior with stored proc / cursors

Any normal SQL Server stored procedure or server-side cursor can wrap an INSQL query — four-part, OPENQUERY, OPENROWSET, parameterized queries, views — to encapsulate complex retrieval. Useful for parameterizing what OPENQUERY itself can't.

Aggregate same data four ways

The four documented ways to summarize:

1. SQL Server AVG(Value) over Cyclic rows — discrete sample average.
2. wwRetrievalMode = 'Average' — time-weighted, the usual right answer for analog process data.
3. AnalogSummaryHistory after configuring summary replication — pre-computed, cheapest at query time.
4. Event-subsystem summary into the SummaryData table — driven by the Event subsystem (which uses simple AVG).

Pick replication when the query rate is high and the summary is stable. Pick the Average mode when ad-hoc.

Next

• 06-alarms-events.md — alarm/event-specific queries.
• 07-rest-api.md — same data, no SQL needed.