37 lines
815 B
Plaintext
37 lines
815 B
Plaintext
-- Enabled vs Disabled Pie Chart WITH time range support
|
|
|
|
WITH filtered AS (
|
|
SELECT
|
|
Name,
|
|
Disabled,
|
|
t_stamp,
|
|
ROW_NUMBER() OVER (PARTITION BY Name ORDER BY t_stamp DESC) AS rn
|
|
FROM lane_data
|
|
WHERE t_stamp BETWEEN :startTime AND :endTime
|
|
),
|
|
|
|
latest_records AS (
|
|
SELECT *
|
|
FROM filtered
|
|
WHERE rn = 1
|
|
),
|
|
|
|
totals AS (
|
|
SELECT
|
|
GREATEST(COUNT(*), 22) AS total_lanes,
|
|
SUM(CASE WHEN Disabled = 1 THEN 1 ELSE 0 END) AS disabled_count
|
|
FROM latest_records
|
|
)
|
|
|
|
SELECT
|
|
'Enabled' AS `Status`,
|
|
ROUND((total_lanes - COALESCE(disabled_count, 0)) * 100.0 / total_lanes, 1) AS `Percentage (%)`
|
|
FROM totals
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
'Disabled' AS `Status`,
|
|
ROUND(COALESCE(disabled_count, 0) * 100.0 / total_lanes, 1) AS `Percentage (%)`
|
|
FROM totals;
|