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';