79 lines
1.5 KiB
SQL
79 lines
1.5 KiB
SQL
WITH filtered AS (
|
|
SELECT
|
|
Name,
|
|
Full,
|
|
Half_Full,
|
|
Jam,
|
|
No_Container,
|
|
Block_Operation,
|
|
t_stamp
|
|
FROM lane_data
|
|
WHERE Name LIKE 'S01%' -- only S01 lanes
|
|
),
|
|
|
|
latest AS (
|
|
-- FAST latest row per lane (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(*), 382) AS total_lanes, -- MIN 382
|
|
COUNT(*) AS real_lane_count,
|
|
SUM(Jam) AS jammed_count,
|
|
SUM(Half_Full) AS half_full_count,
|
|
SUM(Full) AS full_count,
|
|
SUM(No_Container) AS no_container_count,
|
|
SUM(Block_Operation) AS block_operation_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',
|
|
half_full_count,
|
|
ROUND(half_full_count * 100.0 / total_lanes, 2)
|
|
FROM totals
|
|
|
|
UNION ALL
|
|
|
|
-- 100% Full
|
|
SELECT
|
|
'100% Full',
|
|
full_count,
|
|
ROUND(full_count * 100.0 / total_lanes, 2)
|
|
FROM totals
|
|
|
|
UNION ALL
|
|
|
|
-- No Container
|
|
SELECT
|
|
'No Container',
|
|
no_container_count,
|
|
ROUND(no_container_count * 100.0 / total_lanes, 2)
|
|
FROM totals
|
|
|
|
UNION ALL
|
|
|
|
-- Blocked by Operator
|
|
SELECT
|
|
'Blocked by Operator',
|
|
block_operation_count,
|
|
ROUND(block_operation_count * 100.0 / total_lanes, 2)
|
|
FROM totals;
|