mes.spGetAssetStateEventsByInterval

Description

Returns a pivoted view of asset state durations broken out by hour, day, or week intervals. Retrieves state events using `fnGetAssetStateEvents` and bins them into time slices for visual summary analysis. Internally builds dynamic SQL to pivot the results into columns by descriptor (asset + state).

Syntax

mes.spGetAssetStateEventsByInterval @assetId, @startDate, @endDate, @durationSecs, @showChanged, @showDowntimeOnly, @interval, @offsetMins

Parameters:
@assetId (int): ID of the asset to query.
@startDate (datetime): Start of the time range to analyze.
@endDate (datetime): End of the time range to analyze.
@durationSecs (int): Minimum duration (in seconds) for event inclusion.
@showChanged (bit): Whether to filter for changed states only (1 = yes, 0 = no).
@showDowntimeOnly (bit): Whether to restrict to downtime events (1 = yes, 0 = no).
@interval (varchar(10)): Granularity of the time window. One of: 'Hour', 'Day', or 'Week'.
@offsetMins (int): Number of minutes to offset the interval label for display alignment.

Returns:
Pivoted table with:
- `interval`: Time block (formatted as MM-dd HH:mm, MM-dd, etc.)
- One column per asset+state descriptor, with each value being total duration (in minutes) for that interval.

Code Example

-- Get hourly state summaries for asset 4201 for July 29
EXEC mes.spGetAssetStateEventsByInterval
     @assetId = 4201,
     @startDate = '2025-07-29T00:00:00',
     @endDate = '2025-07-30T00:00:00',
     @durationSecs = 30,
     @showChanged = 1,
     @showDowntimeOnly = 0,
     @interval = 'Hour',
     @offsetMins = 0;