38 lines
1013 B
Plaintext
38 lines
1013 B
Plaintext
WITH INDUCTS AS (
|
|
SELECT
|
|
DATE_FORMAT(MIN(s04_timestamp), "%Y-%m-%d %H:00") AS start_timestamp,
|
|
sorter,
|
|
induct,
|
|
CONCAT("H",
|
|
CAST(
|
|
TIMESTAMPDIFF(
|
|
HOUR,
|
|
DATE_FORMAT(MIN(s04_timestamp), "%Y-%m-%d %H:00:00"),
|
|
DATE_FORMAT(LEAST(CURRENT_TIMESTAMP(), :endDate), "%Y-%m-%d %H:00:00")
|
|
) AS CHAR
|
|
)
|
|
) AS hour,
|
|
COUNT(*) AS total
|
|
FROM package_history
|
|
WHERE s04_timestamp BETWEEN :startDate AND :endDate
|
|
GROUP BY sorter, induct, DATE_FORMAT(s04_timestamp, "%Y-%m-%d %H:00")
|
|
), SORTERS AS (
|
|
SELECT
|
|
start_timestamp,
|
|
sorter,
|
|
SUM(total) AS total
|
|
FROM INDUCTS
|
|
GROUP BY sorter, start_timestamp
|
|
)
|
|
SELECT
|
|
I.start_timestamp AS time,
|
|
hour,
|
|
/* Counts: */
|
|
I.total AS total_count,
|
|
/* PPH: */
|
|
I.total AS total_pph,
|
|
/* Percents: */
|
|
ROUND(I.total/S.total, 4) AS 'total/sorter_percent'
|
|
FROM INDUCTS I, SORTERS S
|
|
WHERE induct=:induct AND I.sorter=S.sorter AND I.start_timestamp=S.start_timestamp
|
|
ORDER BY I.start_timestamp ASC; |