55 lines
1.2 KiB
SQL
55 lines
1.2 KiB
SQL
WITH filtered AS (
|
|
-- Only correct S02 lanes (S02_###...)
|
|
SELECT
|
|
Name,
|
|
Full,
|
|
Half_Full,
|
|
Jam,
|
|
t_stamp
|
|
FROM lane_data
|
|
WHERE Name LIKE 'S02\\_%' -- FIXED FILTER
|
|
),
|
|
latest AS (
|
|
-- Get latest row per lane (FAST, NO window functions)
|
|
SELECT f.*
|
|
FROM filtered f
|
|
INNER JOIN (
|
|
SELECT Name, MAX(t_stamp) AS max_ts
|
|
FROM filtered
|
|
GROUP BY Name
|
|
) x ON f.Name = x.Name AND f.t_stamp = x.max_ts
|
|
),
|
|
totals AS (
|
|
SELECT
|
|
GREATEST(COUNT(*), 60) AS total_lanes,
|
|
SUM(Jam) AS jammed_count,
|
|
SUM(Full) AS full_count,
|
|
SUM(Half_Full) AS half_full_count
|
|
FROM latest
|
|
)
|
|
|
|
-- Jammed
|
|
SELECT
|
|
'Jammed' AS `Status`,
|
|
jammed_count AS `Count`,
|
|
ROUND(jammed_count * 100.0 / total_lanes, 2) AS `Percentage (%)`
|
|
FROM totals
|
|
|
|
UNION ALL
|
|
|
|
-- 50% Full
|
|
SELECT
|
|
'50% Full' AS `Status`,
|
|
half_full_count AS `Count`,
|
|
ROUND(half_full_count * 100.0 / total_lanes, 2) AS `Percentage (%)`
|
|
FROM totals
|
|
|
|
UNION ALL
|
|
|
|
-- 100% Full
|
|
SELECT
|
|
'100% Full' AS `Status`,
|
|
full_count AS `Count`,
|
|
ROUND(full_count * 100.0 / total_lanes, 2) AS `Percentage (%)`
|
|
FROM totals;
|