mes.spGetAssetStateEventByShift

Description

Retrieves state events for a specified asset and time range, categorized by shift. Events can be filtered by duration, include only changed states, or be restricted to downtime categories. Includes overlap handling between state and shift boundaries.

Syntax

mes.spGetAssetStateEventByShift @assetId, @rangeStart, @rangeEnd, @durationSecs, @showChanged, @showDowntimeOnly

Parameters:
@assetId (int): ID of the asset to query.
@rangeStart (datetime): Start of the time window.
@rangeEnd (datetime): End of the time window.
@durationSecs (int): Minimum state event duration in seconds to include.
@showChanged (bit): Whether to include changed state events (1 = yes, 0 = no).
@showDowntimeOnly (bit): Whether to include only downtime states (1 = yes, 0 = no).

Returns:
- assetId: ID of the asset.
- assetName: Name of the asset.
- assetPath: Full path of the asset in the hierarchy.
- stateName: Name of the state encountered.
- stateTypeName: Type of the state (e.g., Running, Unplanned Downtime).
- stateCategoryName: Optional category for the state.
- stateDurationSecs: Duration of the state event in seconds (adjusted to shift boundaries).
- shiftId: Shift ID associated with the event.
- shiftName: Human-readable name of the shift.
- startDate / endDate: Trimmed to fit within the shift window.

Code Example

-- Retrieve asset state events by shift for asset 1010 in July 2025
EXEC mes.spGetAssetStateEventByShift 
     @assetId = 1010,
     @rangeStart = '2025-07-01',
     @rangeEnd = '2025-07-31',
     @durationSecs = 120,
     @showChanged = 1,
     @showDowntimeOnly = 0;