-- Main jam results
SELECT 
    Name,
    Jam_count
FROM (
    SELECT
        Name,
        COUNT(*) AS Jam_count
    FROM (
        -- AREA JAMS
        SELECT 
            Name,
            t_stamp
        FROM jam_area
        WHERE t_stamp BETWEEN :starttime AND :endtime

        UNION ALL

        -- DEVICE JAMS with edge detection
        SELECT
            Name,
            t_stamp
        FROM (
            SELECT
                Name,
                t_stamp,
                Jam,
                @prev := IF(@lastName = Name, @prevJam, 0) AS prevJam,
                @prevJam := Jam,
                @lastName := Name
            FROM lane_data,
                (SELECT @prevJam := 0, @lastName := '') AS vars
            WHERE t_stamp BETWEEN :starttime AND :endtime
            ORDER BY Name, t_stamp
        ) x
        WHERE Jam = 1 AND prevJam = 0

    ) all_jams
    GROUP BY Name
) jam_results

UNION ALL

-- Fallback row if nothing exists
SELECT 
    'N/A' AS Name,
    0 AS Jam_count
WHERE NOT EXISTS (
    SELECT 1
    FROM (
        -- AREA JAMS
        SELECT 
            Name
        FROM jam_area
        WHERE t_stamp BETWEEN :starttime AND :endtime

        UNION ALL

        -- DEVICE JAMS
        SELECT 
            Name
        FROM lane_data
        WHERE t_stamp BETWEEN :starttime AND :endtime
          AND Jam = 1
    ) t
);
