mes.spGetWorkOrderDataSummary
Description
This stored procedure summarizes key production and performance metrics for a specific asset and work order within a defined date range. It calculates availability, performance, and quality KPIs using production and counter data joined with work order and item information.Syntax
mes.spGetWorkOrderDataSummary @assetId, @rangeStart, @rangeEnd, @workOrderIdParameters:
@assetId (int): The asset ID for which data is retrieved.
@rangeStart (datetime): The start of the reporting window.
@rangeEnd (datetime): The end of the reporting window.
@workOrderId (int): The specific work order to summarize.
Returns:
A row per work order and item with the following fields:
- **targetCount**: Expected production count based on runtime and rate
- **infeedCount / outfeedCount / wasteCount**: Raw and derived production totals
- **runtimeSecs / unplannedDowntimeSecs / plannedDowntimeSecs / durationSecs**: Time-based metrics
- **tgtOEE / tgtA / tgtP / tgtQ / tgtThreshold**: Work order KPI targets
- **availability**: runtimeSecs / (runtimeSecs + unplannedDowntimeSecs)
- **performance**: infeedCount / targetCount
- **quality**: (infeedCount - wasteCount) / infeedCount
Code Example
EXEC mes.spGetWorkOrderDataSummary
@assetId = 304,
@rangeStart = '2025-07-01',
@rangeEnd = '2025-07-30',
@workOrderId = 12345;