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;