60 lines
3.7 KiB
Plaintext
60 lines
3.7 KiB
Plaintext
-- Hourly Sorter Details Graph Query (Rate)
|
|
-- Shows every hour in detail for graphing with all sorter statistics as rates (items per hour)
|
|
-- Rate calculation matches SorterDetails_Rate.sql formula
|
|
|
|
SELECT
|
|
CONCAT('H', TIMESTAMPDIFF(HOUR,
|
|
DATE_FORMAT(Startstamp, "%Y-%m-%d %H:00:00"),
|
|
DATE_FORMAT(NOW(), "%Y-%m-%d %H:00:00")
|
|
)) AS Hour,
|
|
ROUND(inducted_count * 3600 / TIMESTAMPDIFF(SECOND, Startstamp, Endtstamp), 2) AS Inducted,
|
|
ROUND(sorted_count * 3600 / TIMESTAMPDIFF(SECOND, Startstamp, Endtstamp), 2) AS Sorted,
|
|
ROUND(unknown_count * 3600 / TIMESTAMPDIFF(SECOND, Startstamp, Endtstamp), 2) AS Unknown,
|
|
ROUND(unexpected_count * 3600 / TIMESTAMPDIFF(SECOND, Startstamp, Endtstamp), 2) AS Unexpected,
|
|
ROUND(tracking_error_count * 3600 / TIMESTAMPDIFF(SECOND, Startstamp, Endtstamp), 2) AS TrackingError,
|
|
ROUND(gap_error_count * 3600 / TIMESTAMPDIFF(SECOND, Startstamp, Endtstamp), 2) AS GapError,
|
|
ROUND(destination_full_count * 3600 / TIMESTAMPDIFF(SECOND, Startstamp, Endtstamp), 2) AS DestinationFull,
|
|
ROUND(destination_fault_count * 3600 / TIMESTAMPDIFF(SECOND, Startstamp, Endtstamp), 2) AS DestinationFault,
|
|
ROUND(destination_invalid_count * 3600 / TIMESTAMPDIFF(SECOND, Startstamp, Endtstamp), 2) AS DestinationInvalid,
|
|
ROUND(destination_disabled_count * 3600 / TIMESTAMPDIFF(SECOND, Startstamp, Endtstamp), 2) AS DestinationDisabled,
|
|
ROUND(throughput_limit_count * 3600 / TIMESTAMPDIFF(SECOND, Startstamp, Endtstamp), 2) AS ThroughputLimit,
|
|
ROUND(divert_fail_count * 3600 / TIMESTAMPDIFF(SECOND, Startstamp, Endtstamp), 2) AS DivertFail,
|
|
ROUND(destination_none_count * 3600 / TIMESTAMPDIFF(SECOND, Startstamp, Endtstamp), 2) AS DestinationNone,
|
|
ROUND(lost_count * 3600 / TIMESTAMPDIFF(SECOND, Startstamp, Endtstamp), 2) AS Lost,
|
|
ROUND(dimension_error_count * 3600 / TIMESTAMPDIFF(SECOND, Startstamp, Endtstamp), 2) AS DimensionError,
|
|
ROUND(weight_error_count * 3600 / TIMESTAMPDIFF(SECOND, Startstamp, Endtstamp), 2) AS WeightError,
|
|
ROUND(container_util_count * 3600 / TIMESTAMPDIFF(SECOND, Startstamp, Endtstamp), 2) AS ContainerUtilization,
|
|
ROUND(unable_to_divert_count * 3600 / TIMESTAMPDIFF(SECOND, Startstamp, Endtstamp), 2) AS UnableToDivert,
|
|
ROUND(dest_not_attempted_count * 3600 / TIMESTAMPDIFF(SECOND, Startstamp, Endtstamp), 2) AS DestinationNotAttempted,
|
|
ROUND(scan_error_count * 3600 / TIMESTAMPDIFF(SECOND, Startstamp, Endtstamp), 2) AS ScanError
|
|
FROM (
|
|
SELECT
|
|
MIN(t_stamp) AS Startstamp,
|
|
MAX(t_stamp) AS Endtstamp,
|
|
COUNT(*) AS inducted_count,
|
|
SUM(DivertStatus = 0) AS sorted_count,
|
|
SUM(DivertStatus = 1) AS unknown_count,
|
|
SUM(DivertStatus = 2) AS unexpected_count,
|
|
SUM(DivertStatus = 3) AS tracking_error_count,
|
|
SUM(DivertStatus = 4) AS gap_error_count,
|
|
SUM(DivertStatus = 5) AS destination_full_count,
|
|
SUM(DivertStatus = 6) AS destination_fault_count,
|
|
SUM(DivertStatus = 7) AS destination_invalid_count,
|
|
SUM(DivertStatus = 12) AS destination_disabled_count,
|
|
SUM(DivertStatus = 13) AS throughput_limit_count,
|
|
SUM(DivertStatus = 14) AS divert_fail_count,
|
|
SUM(DivertStatus = 16) AS destination_none_count,
|
|
SUM(DivertStatus = 17) AS lost_count,
|
|
SUM(DivertStatus = 18) AS dimension_error_count,
|
|
SUM(DivertStatus = 19) AS weight_error_count,
|
|
SUM(DivertStatus = 20) AS container_util_count,
|
|
SUM(DivertStatus = 21) AS unable_to_divert_count,
|
|
SUM(DivertStatus = 22) AS dest_not_attempted_count,
|
|
SUM(DivertStatus IN (8, 9, 10)) AS scan_error_count
|
|
FROM alltable
|
|
WHERE t_stamp BETWEEN :starttime AND :endtime
|
|
GROUP BY DATE(t_stamp), HOUR(t_stamp)
|
|
) counts
|
|
ORDER BY Startstamp ASC;
|
|
|