41 lines
1.3 KiB
Plaintext
41 lines
1.3 KiB
Plaintext
WITH SCANNERS AS (
|
|
SELECT
|
|
MIN(s04_timestamp) AS start_timestamp,
|
|
MAX(s04_timestamp) AS end_timestamp,
|
|
3600/TIMESTAMPDIFF(SECOND, :startDate, :endDate) AS pph_multiplier,
|
|
sorter,
|
|
scanner,
|
|
COUNT(*) AS total,
|
|
SUM(scanner_status NOT IN ("No Read", "No Code", "Multi Label")) AS good_read,
|
|
SUM(scanner_status = "No Read") AS no_read,
|
|
SUM(scanner_status = "No Code") AS no_code,
|
|
SUM(scanner_status = "Multi Label") AS multi_label
|
|
FROM package_history
|
|
WHERE s04_timestamp BETWEEN :startDate AND :endDate
|
|
GROUP BY sorter, scanner
|
|
ORDER BY sorter, scanner
|
|
)
|
|
SELECT
|
|
start_timestamp,
|
|
end_timestamp,
|
|
sorter AS sorter,
|
|
scanner AS scanner,
|
|
/* Counts: */
|
|
total AS total_count,
|
|
good_read AS good_read_count,
|
|
no_read AS no_read_count,
|
|
no_code AS no_code_count,
|
|
multi_label AS multi_label_count,
|
|
/* PPH: */
|
|
ROUND(total*pph_multiplier) AS total_pph,
|
|
ROUND(good_read*pph_multiplier) AS good_read_pph,
|
|
ROUND(no_read*pph_multiplier) AS no_read_pph,
|
|
ROUND(no_code*pph_multiplier) AS no_code_pph,
|
|
ROUND(multi_label*pph_multiplier) AS multi_label_pph,
|
|
/* Percents: */
|
|
ROUND(total/total, 4) AS total_percent,
|
|
ROUND(good_read/total, 4) AS good_read_percent,
|
|
ROUND(no_read/total, 4) AS no_read_percent,
|
|
ROUND(no_code/total, 4) AS no_code_percent,
|
|
ROUND(multi_label/total, 4) AS multi_label_percent
|
|
FROM SCANNERS; |