22 lines
707 B
Plaintext
22 lines
707 B
Plaintext
-- Calculate Maximum PPH (Parcels Per Hour) in 5-minute intervals
|
|
-- Where requested destination matches actual destination
|
|
-- PPH = (count in 5 minutes) * 12
|
|
|
|
SELECT
|
|
MAX(pph) as max_pph,
|
|
MAX(interval_start) as peak_time,
|
|
SUM(parcel_count) as total_parcels
|
|
FROM (
|
|
SELECT
|
|
DATE_FORMAT(t_stamp, '%Y-%m-%d %H:%i:00') as interval_start,
|
|
FLOOR(MINUTE(t_stamp) / 5) as five_min_group,
|
|
COUNT(*) as parcel_count,
|
|
COUNT(*) * 12 as pph
|
|
FROM item_data
|
|
WHERE t_stamp BETWEEN :starttime AND :endtime
|
|
AND asRequested_Dest_ID_0 = sActual_Dest_ID
|
|
GROUP BY
|
|
DATE_FORMAT(t_stamp, '%Y-%m-%d %H:'),
|
|
FLOOR(MINUTE(t_stamp) / 5)
|
|
) as intervals;
|