mes.spGetOEEDataByIntervalGroupByShift

Description

This stored procedure calculates and returns Overall Equipment Effectiveness (OEE) metrics for a given asset over a time range, grouped by shift. It computes runtime, downtime, counter-based performance, and derives availability, performance, quality, and OEE. It aggregates the data by time intervals (hour/day/week) and shifts.

Syntax

mes.spGetOEEDataByIntervalGroupByShift @assetId, @startDate, @endDate, @interval

Parameters:
@assetId (int): Asset identifier for which to compute OEE
@startDate (datetime): Start of the date range
@endDate (datetime): End of the date range
@interval (varchar): Time interval for aggregation ('Hour', 'Day', or 'Week')

Returns:
One row per shift per interval with fields:
- Shift ID and name
- Time interval (hourTimestamp, duration)
- Runtime and downtime (planned/unplanned)
- Standard rate and package/item units
- Infeed, outfeed, and waste counts
- Target production and efficiency metrics:
- **Availability** = runtime / duration
- **Performance** = infeed / (standardRate × runtime)
- **Quality** = (infeed - waste) / infeed
- **OEE** = Availability × Performance × Quality

Code Example

EXEC mes.spGetOEEDataByIntervalGroupByShift
    @assetId = 1001,
    @startDate = '2025-07-01',
    @endDate = '2025-07-15',
    @interval = 'Day';