SELECT 
    COALESCE(data.roundtime, 'N/A') AS StartTimestamp,
    COALESCE(
        CONCAT(
            'H',
            TIMESTAMPDIFF(
                HOUR,
                DATE_FORMAT(data.roundtime, "%Y-%m-%d %H:00:00"),
                DATE_FORMAT(NOW(), "%Y-%m-%d %H:00:00")
            )
        ),
        'N/A'
    ) AS Hour,
    COALESCE(data.sScanner_Name, 'N/A') AS sScanner_Name,

    data.BadReads,
    data.GoodReads,
    data.MultiReads,
    data.NoData,
    data.NoReads,
    COALESCE(data.Total, 0) AS Total

FROM (SELECT 1) AS p
LEFT JOIN (
    SELECT 
        CONCAT(DATE(t_stamp), ' ', HOUR(t_stamp), ':00') AS roundtime,
        'S03aa' AS sScanner_Name,

        COUNT(*) AS Total,

        CONCAT(
            CASE WHEN COUNT(*) = 0 THEN 0
                 ELSE ROUND(
                     (SUM(CASE WHEN adiSort_Code_0 NOT IN (0, 8, 9, 10, 11, 15) THEN 1 ELSE 0 END) * 100.0)
                     / COUNT(*), 2)
            END, '%'
        ) AS BadReads,

        CONCAT(
            CASE WHEN COUNT(*) = 0 THEN 0
                 ELSE ROUND(
                     (SUM(CASE WHEN adiSort_Code_0 = 0 THEN 1 ELSE 0 END) * 100.0)
                     / COUNT(*), 2)
            END, '%'
        ) AS GoodReads,

        CONCAT(
            CASE WHEN COUNT(*) = 0 THEN 0
                 ELSE ROUND(
                     (SUM(CASE WHEN adiSort_Code_0 = 10 THEN 1 ELSE 0 END) * 100.0)
                     / COUNT(*), 2)
            END, '%'
        ) AS MultiReads,

        CONCAT(
            CASE WHEN COUNT(*) = 0 THEN 0
                 ELSE ROUND(
                     (SUM(CASE WHEN adiSort_Code_0 = 9 THEN 1 ELSE 0 END) * 100.0)
                     / COUNT(*), 2)
            END, '%'
        ) AS NoData,

        CONCAT(
            CASE WHEN COUNT(*) = 0 THEN 0
                 ELSE ROUND(
                     (SUM(CASE WHEN adiSort_Code_0 = 8 THEN 1 ELSE 0 END) * 100.0)
                     / COUNT(*), 2)
            END, '%'
        ) AS NoReads

    FROM item_data
    WHERE t_stamp BETWEEN :starttime AND :endtime
        AND adiSort_Code_0 NOT IN (11, 15)
        AND sLocation_ID LIKE 'S03%'
        AND (:scannername IS NULL OR :scannername = '' OR :scannername = 'S03aa')

    GROUP BY DATE(t_stamp), HOUR(t_stamp)
) AS data ON 1=1
ORDER BY data.roundtime ASC;