Event SQL Patterns

Pattern 1 — batch events from a batch table#

The MES writes one row per batch with start and end stamps. Two triggers, one query shape each:

-- START trigger: batches that began in the window
SELECT BatchStart AS [DateTime]
FROM   Batches
WHERE  Line = '[ModelAttribute("Line Number")]'
  AND  BatchStart >  [PeriodStart]
  AND  BatchStart <= [PeriodEnd]

-- END trigger: batches that finished in the window
SELECT BatchEnd AS [DateTime]
FROM   Batches
WHERE  Line = '[ModelAttribute("Line Number")]'
  AND  BatchEnd IS NOT NULL
  AND  BatchEnd >  [PeriodStart]
  AND  BatchEnd <= [PeriodEnd]

The IS NOT NULL on the end matters: an in-progress batch has no end yet, and a NULL arriving as a transition produces exactly the stuck-open events you were trying to avoid.

Late-completed rows (a batch confirmed retrospectively) are caught by the scheme's Refresh Offset — the window re-examined every pass must exceed the worst confirmation lag. For rows completed days later, a refresh trigger is the better tool than a giant offset.

Pattern 2 — a schedule event from a shift/schedule table#

Asset schedules where "no row" means a default 24-hour day:

-- START trigger: today's scheduled start, as an absolute timestamp
SELECT CAST(CAST(s.ScheduleDate AS date) AS datetime)
         + CAST(COALESCE(s.StartTime, '00:00:00') AS datetime) AS [DateTime]
FROM   Schedules s
WHERE  s.Asset = '[ModelAttribute("Asset")]'
  AND  CAST(s.ScheduleDate AS date)
         + CAST(COALESCE(s.StartTime,'00:00:00') AS datetime) >  [PeriodStart]
  AND  CAST(s.ScheduleDate AS date)
         + CAST(COALESCE(s.StartTime,'00:00:00') AS datetime) <= [PeriodEnd]

The whole trick is the date+time composition: a schedule table stores TIME columns, and a bare 06:00:00 returned to Infohub matches nothing — rule 1 again. Compose the absolute timestamp in the query.

Overnight shifts (end before start): add one day to the end when EndTime < StartTime — DATEADD(day, CASE WHEN EndTime < StartTime THEN 1 ELSE 0 END, ...).

Pattern 3 — attribute lookup with multiple keys#

An event attribute fetching a target value keyed by two things the event knows — say product and mixer:

SELECT TargetTime AS Value
FROM   ProductTargets
WHERE  ProductCode = '[AttributeValue]'          -- from the event's Product attribute
  AND  Mixer       = '[ModelAttribute("Mixer")]' -- from the model position

The rule of thumb for "which token": what varies per event comes from an attribute ([AttributeValue], with the attribute dependency declared so the lookup runs after the product is known); what varies per instance comes from [ModelAttribute]. A lookup needing two event-level keys means the second key should itself be an attribute the scheme captures first — attribute dependencies chain.

Pattern 4 — reference data that is not time-based#

Product master data — ID, name, target rate — with no timestamps at all. The honest shape: this is an event whose trigger is the appearance/change of the row, and whose attributes carry the reference fields.

-- START trigger: fire when a row is created or changed
SELECT LastModified AS [DateTime]
FROM   Products
WHERE  LastModified >  [PeriodStart]
  AND  LastModified <= [PeriodEnd]

…with attributes querying the same row for name, target, and so on. It works because the master table has a modified-stamp. If it has no timestamp column at all, Infohub cannot poll it into events reliably — add a modified-stamp at the source, or accept a manual-attribute approach. That limitation is worth knowing before designing around it.

Pattern 5 — event boundaries from a changing value (batch ID change)#

When there is no start/end table, only a current-batch-ID column, the transition is the change:

-- START trigger: moments the batch id changed
SELECT r.Stamp AS [DateTime]
FROM   Readings r
WHERE  r.Machine = '[ModelAttribute("Machine")]'
  AND  r.Stamp >  [PeriodStart]
  AND  r.Stamp <= [PeriodEnd]
  AND  r.BatchID <> (SELECT TOP 1 p.BatchID FROM Readings p
                     WHERE p.Machine = r.Machine AND p.Stamp < r.Stamp
                     ORDER BY p.Stamp DESC)

Expensive without an index on (Machine, Stamp) — as with all of these, the query runs per scheme per pass, so test it with real bounds and look at the plan before deploying. See SQL Databases for the performance rules.

Debugging any of these#

1. Run the query manually with real window values substituted. No rows → the query, not Infohub.
2. Check the returned column is a full datetime inside the window.
3. Check the composed timestamps' zone matches what Infohub expects — a schedule table in local time against a UTC window shifts every event.
4. If events appear late or need correcting, raise the Refresh Offset or add a refresh trigger — do not schedule backfills.
5. Watch for NULLs: any NULL reaching a trigger or attribute lookup produces the least-debuggable failures. COALESCE at the query is cheaper than diagnosing it in Infohub.

See also#

• Event Triggers
• SQL Databases
• Event Attributes and Enumerations
• Refresh Triggers