WITH SORTERS AS (
	SELECT
		DATE_FORMAT(MIN(s04_timestamp), "%Y-%m-%d %H:%i:00") AS time,
		COUNT(*) AS total,
		/* PPH Data */
		SUM(sort_code="Success") AS success,
		SUM(sort_code IN ("Dest Invalid", "Dest None", "Underutilized")) AS awcs,
		SUM(sort_code IN ("Dest Disabled", "Dest Full", "Dim Error", "Unexpected", "Weight Err")) AS operational,
		SUM(sort_code IN ("Dest Fault", "Div Fail", "Gap Err", "Lost", "Rate High", "Track Err", "Unknown", "Unsafe")) AS machine,
		SUM(sort_code IN ("No Read", "No Code", "Multi Label")) AS scanner,
       	/* Problem Solve Data */
		SUM(sort_code="No Read") AS no_read,
		SUM(sort_code="No Code") AS no_code,
		SUM(sort_code="Multi Label") AS multi_label,
		/* Sorter Error Data */
		SUM(sort_code="Gap Err") AS gap_err,
		SUM(sort_code="Div Fail") AS div_fail,
		SUM(sort_code="Dest None") AS dest_none,
		SUM(sort_code="Lost") AS lost
	FROM package_history
	WHERE s04_timestamp BETWEEN :startDate AND :endDate
      AND sorter = :sorter
	GROUP BY DATE_FORMAT(s04_timestamp, "%Y-%m-%d %H:%i:00")
    ORDER BY DATE_FORMAT(s04_timestamp, "%Y-%m-%d %H:%i:00")
), DATA AS (
SELECT
	total,
	/* PPH: */
	ROUND((SUM((total - machine - scanner)*60) OVER w)/(:movAvgMin+1)) AS total_pph,
    /* Problem Solve Percents: */
	no_read+no_code+multi_label AS scanner,
	/* Sorter Error Percents: */
	gap_err+div_fail+dest_none+lost AS sorter
FROM SORTERS
WINDOW w AS (ORDER BY STR_TO_DATE(time, "%Y-%m-%d %H:%i:00") RANGE BETWEEN INTERVAL :movAvgMin MINUTE PRECEDING AND CURRENT ROW)
)
SELECT
	MAX(total_pph) AS total_pph,
    /* Problem Solve Percents: */
	ROUND(SUM(scanner)/SUM(total), 4) AS scanner,
	/* Sorter Error Percents: */
	ROUND(SUM(sorter)/SUM(total), 4) AS sorter
FROM DATA;