55 lines
5.2 KiB
Plaintext
55 lines
5.2 KiB
Plaintext
SELECT
|
|
COALESCE(data.roundtime, 'N/A') AS StartTimestamp,
|
|
COALESCE(CONCAT('H', TIMESTAMPDIFF(HOUR, DATE_FORMAT(data.roundtime, "%Y-%m-%d %H:00:00"), DATE_FORMAT(NOW(), "%Y-%m-%d %H:00:00"))), 'N/A') AS Hour,
|
|
COALESCE(data.sLocation_ID, 'N/A') AS sLocation_ID,
|
|
COALESCE(data.success_perc, '0%') AS success_perc,
|
|
COALESCE(data.unknown_perc, '0%') AS unknown_perc,
|
|
COALESCE(data.unexpected_container_perc, '0%') AS unexpected_container_perc,
|
|
COALESCE(data.tracking_error_perc, '0%') AS tracking_error_perc,
|
|
COALESCE(data.gap_error_perc, '0%') AS gap_error_perc,
|
|
COALESCE(data.destination_full_perc, '0%') AS destination_full_perc,
|
|
COALESCE(data.destination_non_operational_perc, '0%') AS destination_non_operational_perc,
|
|
COALESCE(data.invalid_destination_perc, '0%') AS invalid_destination_perc,
|
|
COALESCE(data.destination_disabled_perc, '0%') AS destination_disabled_perc,
|
|
COALESCE(data.throughput_limit_perc, '0%') AS throughput_limit_perc,
|
|
COALESCE(data.failed_to_divert_perc, '0%') AS failed_to_divert_perc,
|
|
COALESCE(data.no_destination_received_perc, '0%') AS no_destination_received_perc,
|
|
COALESCE(data.lost_container_perc, '0%') AS lost_container_perc,
|
|
COALESCE(data.dimension_error_perc, '0%') AS dimension_error_perc,
|
|
COALESCE(data.weight_error_perc, '0%') AS weight_error_perc,
|
|
COALESCE(data.container_utilization_perc, '0%') AS container_utilization_perc,
|
|
COALESCE(data.unable_to_divert_perc, '0%') AS unable_to_divert_perc,
|
|
COALESCE(data.destination_not_attempted_perc, '0%') AS destination_not_attempted_perc,
|
|
COALESCE(data.scan_error_perc, '0%') AS scan_error_perc
|
|
FROM (SELECT 1) AS p
|
|
LEFT JOIN (
|
|
SELECT
|
|
CONCAT(DATE(t_stamp), ' ', HOUR(t_stamp), ':00') AS roundtime,
|
|
sLocation_ID,
|
|
CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 0 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS success_perc,
|
|
CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 1 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS unknown_perc,
|
|
CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 2 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS unexpected_container_perc,
|
|
CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 3 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS tracking_error_perc,
|
|
CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 4 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS gap_error_perc,
|
|
CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 5 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS destination_full_perc,
|
|
CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 6 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS destination_non_operational_perc,
|
|
CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 7 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS invalid_destination_perc,
|
|
CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 12 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS destination_disabled_perc,
|
|
CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 13 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS throughput_limit_perc,
|
|
CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 14 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS failed_to_divert_perc,
|
|
CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 16 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS no_destination_received_perc,
|
|
CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 17 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS lost_container_perc,
|
|
CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 18 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS dimension_error_perc,
|
|
CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 19 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS weight_error_perc,
|
|
CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 20 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS container_utilization_perc,
|
|
CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 21 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS unable_to_divert_perc,
|
|
CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 22 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS destination_not_attempted_perc,
|
|
CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 IN (8, 9, 10) THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS scan_error_perc
|
|
FROM item_data
|
|
WHERE t_stamp BETWEEN :starttime AND :endtime
|
|
AND adiSort_Code_0 NOT IN (11, 15)
|
|
AND (sLocation_ID = :locationid OR :locationid IS NULL OR :locationid = '')
|
|
GROUP BY DATE(t_stamp), HOUR(t_stamp), sLocation_ID
|
|
) AS data ON 1=1
|
|
ORDER BY data.roundtime ASC;
|