WITH SORTERS AS (
	SELECT
		DATE_FORMAT(MIN(s04_timestamp), "%Y-%m-%d %H:00") AS start_timestamp,
	    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,
        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
	FROM package_history
	WHERE s04_timestamp BETWEEN :startDate AND :endDate
	  AND sorter=:sorter
	GROUP BY DATE_FORMAT(s04_timestamp, "%Y-%m-%d %H:00")
)
SELECT
	start_timestamp,
	hour,
	/* Counts: */
	total AS total_count,
	success AS success_count,
	awcs AS awcs_issues_count,
	operational AS operational_issues_count,
	machine AS machine_issues_count,
	scanner AS scanner_issues_count,
	/* PPH: */
	total AS total_pph,
	success AS success_pph,
	awcs AS awcs_issues_pph,
	operational AS operational_issues_pph,
	machine AS machine_issues_pph,
	scanner AS scanner_issues_pph,
	/* Percents: */
	ROUND(total/total, 4) AS total_percent,
	ROUND(success/total, 4) AS success_percent,
	ROUND(awcs/total, 4) AS awcs_issues_percent,
	ROUND(operational/total, 4) AS operational_issues_percent,
	ROUND(machine/total, 4) AS machine_issues_percent,
	ROUND(scanner/total, 4) AS scanner_issues_percent
FROM SORTERS
ORDER BY start_timestamp;