47 lines
1.4 KiB
Plaintext
47 lines
1.4 KiB
Plaintext
SELECT
|
|
T.StartTimestamp,
|
|
T.EndTimestamp,
|
|
T.Total,
|
|
|
|
-- Percentage for Merge_MCM01
|
|
CASE WHEN T.Total = 0
|
|
THEN '0%'
|
|
ELSE CONCAT(ROUND(T.MergeCount / T.Total * 100, 2), '%')
|
|
END AS Merge_MCM01,
|
|
|
|
-- Percentage for Transport_MCM01
|
|
CASE WHEN T.Total = 0
|
|
THEN '0%'
|
|
ELSE CONCAT(ROUND(T.TransportCount / T.Total * 100, 2), '%')
|
|
END AS Transport_MCM01,
|
|
|
|
-- Percentage for Sorter_MCM02
|
|
CASE WHEN T.Total = 0
|
|
THEN '0%'
|
|
ELSE CONCAT(ROUND(T.SorterCount / T.Total * 100, 2), '%')
|
|
END AS Sorter_MCM02
|
|
|
|
FROM (
|
|
-- Inner query (T) calculates the total and individual counts, making the outer percentage calculation cleaner
|
|
SELECT
|
|
DATE_FORMAT(:starttime, '%Y-%m-%d %H:%i') AS StartTimestamp,
|
|
DATE_FORMAT(:endtime, '%Y-%m-%d %H:%i') AS EndTimestamp,
|
|
|
|
-- Individual counts (COALESCE handles NULL if the subquery returns no rows)
|
|
COALESCE(c.MergeCount, 0) AS MergeCount,
|
|
COALESCE(c.TransportCount, 0) AS TransportCount,
|
|
COALESCE(c.SorterCount, 0) AS SorterCount,
|
|
|
|
-- Calculate Total once
|
|
(COALESCE(c.MergeCount, 0) + COALESCE(c.TransportCount, 0) + COALESCE(c.SorterCount, 0)) AS Total
|
|
|
|
FROM (
|
|
-- Subquery (c) aggregates the jam counts over the time range
|
|
SELECT
|
|
SUM(Merge_MCM01 = 1) AS MergeCount,
|
|
SUM(Transport_MCM01 = 1) AS TransportCount,
|
|
SUM(Sorter_MCM02 = 1) AS SorterCount
|
|
FROM jam_area
|
|
WHERE t_stamp BETWEEN :starttime AND :endtime
|
|
) AS c
|
|
) AS T; |