WITH LANES_SUCCESS AS (
	SELECT
		MIN(s04_timestamp) AS start_timestamp,
		MAX(s04_timestamp) AS end_timestamp,
		sorter,
		destination_act AS lane,
		COUNT(*) AS total
	FROM package_history a
	WHERE s04_timestamp BETWEEN :startDate AND :endDate
	GROUP BY sorter, s04_act_dest
), LANES_FAIL_REQ AS (
	SELECT
		MIN(s04_timestamp) AS start_timestamp,
		MAX(s04_timestamp) AS end_timestamp,
		sorter,
		CONCAT(sorter, LPAD(s02_req_dest, 2, '0')) AS lane,
		SUM(req_dest_reason=5) AS dest_full,
		SUM(req_dest_reason=6) AS dest_fault,
		SUM(req_dest_reason=12) AS dest_disabled
	FROM package_history
	WHERE s04_timestamp BETWEEN :startDate AND :endDate
	  AND s04_act_dest != s02_req_dest
	GROUP BY sorter, s02_req_dest
), LANES_FAIL_ALT AS (
	SELECT
		MIN(s04_timestamp) AS start_timestamp,
		MAX(s04_timestamp) AS end_timestamp,
		sorter,
		CONCAT(sorter, LPAD(s02_alt_dest, 2, '0')) AS lane,
		SUM(alt_dest_reason=5) AS dest_full,
		SUM(alt_dest_reason=6) AS dest_fault,
		SUM(alt_dest_reason=12) AS dest_disabled
	FROM package_history
	WHERE s04_timestamp BETWEEN :startDate AND :endDate
	  AND s04_act_dest != s02_alt_dest
	GROUP BY sorter, s02_alt_dest
), LANES AS (
	SELECT
		LEAST(s.start_timestamp, IFNULL(r.start_timestamp, s.start_timestamp), IFNULL(r.start_timestamp, a.start_timestamp)) AS start_timestamp,
		GREATEST(s.end_timestamp, IFNULL(r.end_timestamp, s.end_timestamp), IFNULL(r.end_timestamp, a.end_timestamp)) AS end_timestamp,
		3600/TIMESTAMPDIFF(SECOND, :startDate, :endDate) AS pph_multiplier,
		s.sorter,
		s.lane,
		s.total,
		IFNULL(r.dest_full, 0)+IFNULL(a.dest_full, 0) AS dest_full,
		IFNULL(r.dest_fault, 0)+IFNULL(a.dest_fault, 0) AS dest_fault,
		IFNULL(r.dest_disabled, 0)+IFNULL(a.dest_disabled, 0) AS dest_disabled
	FROM LANES_SUCCESS s
	LEFT JOIN LANES_FAIL_REQ r ON s.sorter=r.sorter AND s.lane=r.lane
	LEFT JOIN LANES_FAIL_ALT a ON s.sorter=a.sorter AND s.lane=a.lane
)
SELECT
	start_timestamp,
	end_timestamp,
    sorter,
    lane,
	/* Counts: */
	total AS total_count,
	dest_full AS dest_full_count,
	dest_fault AS dest_fault_count,
	dest_disabled AS dest_disabled_count,
	/* PPH: */
	ROUND(total*pph_multiplier) AS total_pph,
	ROUND(dest_full*pph_multiplier) AS dest_full_pph,
	ROUND(dest_fault*pph_multiplier) AS dest_fault_pph,
	ROUND(dest_disabled*pph_multiplier) AS dest_disabled_pph,
	/* Percents: */
	ROUND(total/total, 4) AS total_percent,
	ROUND(dest_full/total, 4) AS dest_full_percent,
	ROUND(dest_fault/total, 4) AS dest_fault_percent,
	ROUND(dest_disabled/total, 4) AS dest_disabled_percent
FROM LANES
ORDER BY sorter, lane;