SELECT
    roundtime AS `Round Time`,
    sActual_Dest_ID AS `Lane`,
    COALESCE(Success, 0) AS `Success Count`,
    COALESCE(Unknown, 0) AS `Unknown Count`,
    COALESCE(Unexpected_Container, 0) AS `Unexpected Container Count`,
    COALESCE(Tracking_Error, 0) AS `Tracking Error Count`,
    COALESCE(Gap_Error, 0) AS `Gap Error Count`,
    COALESCE(Destination_Full, 0) AS `Destination Full Count`,
    COALESCE(Destination_Non_Operational, 0) AS `Destination Non Operational Count`,
    COALESCE(Invalid_Destination, 0) AS `Invalid Destination Count`,
    COALESCE(Scanner_Error, 0) AS `Scanner Error Count`,
    COALESCE(Destination_Disabled, 0) AS `Destination Disabled Count`,
    COALESCE(Throughput_Limit, 0) AS `Throughput Limit Count`,
    COALESCE(Failed_To_Divert, 0) AS `Failed To Divert Count`,
    COALESCE(No_Destination_Received, 0) AS `No Destination Received Count`,
    COALESCE(Lost_Container, 0) AS `Lost Container Count`,
    COALESCE(Dimension_Error, 0) AS `Dimension Error Count`,
    COALESCE(Weight_Error, 0) AS `Weight Error Count`,
    COALESCE(Container_Utilization, 0) AS `Container Utilization Count`,
    COALESCE(Unable_To_Divert, 0) AS `Unable To Divert Count`,
    COALESCE(Destination_Not_Attempted, 0) AS `Destination Not Attempted Count`
FROM (
    SELECT
        FROM_UNIXTIME(
            FLOOR(UNIX_TIMESTAMP(t_stamp) / 
                CEIL(TIMESTAMPDIFF(SECOND, :starttime, :endtime) / 24.0)
            ) * 
            CEIL(TIMESTAMPDIFF(SECOND, :starttime, :endtime) / 24.0)
        ) AS roundtime,
        sActual_Dest_ID,
        SUM(CASE WHEN adiSort_Code_0 = 0 THEN 1 ELSE 0 END) AS Success,
        SUM(CASE WHEN adiSort_Code_0 = 1 THEN 1 ELSE 0 END) AS Unknown,
        SUM(CASE WHEN adiSort_Code_0 = 2 THEN 1 ELSE 0 END) AS Unexpected_Container,
        SUM(CASE WHEN adiSort_Code_0 = 3 THEN 1 ELSE 0 END) AS Tracking_Error,
        SUM(CASE WHEN adiSort_Code_0 = 4 THEN 1 ELSE 0 END) AS Gap_Error,
        SUM(CASE WHEN adiSort_Code_0 = 5 THEN 1 ELSE 0 END) AS Destination_Full,
        SUM(CASE WHEN adiSort_Code_0 = 6 THEN 1 ELSE 0 END) AS Destination_Non_Operational,
        SUM(CASE WHEN adiSort_Code_0 = 7 THEN 1 ELSE 0 END) AS Invalid_Destination,
        SUM(CASE WHEN adiSort_Code_0 IN (8, 9, 10) THEN 1 ELSE 0 END) AS Scanner_Error,
        SUM(CASE WHEN adiSort_Code_0 = 12 THEN 1 ELSE 0 END) AS Destination_Disabled,
        SUM(CASE WHEN adiSort_Code_0 = 13 THEN 1 ELSE 0 END) AS Throughput_Limit,
        SUM(CASE WHEN adiSort_Code_0 = 14 THEN 1 ELSE 0 END) AS Failed_To_Divert,
        SUM(CASE WHEN adiSort_Code_0 = 16 THEN 1 ELSE 0 END) AS No_Destination_Received,
        SUM(CASE WHEN adiSort_Code_0 = 17 THEN 1 ELSE 0 END) AS Lost_Container,
        SUM(CASE WHEN adiSort_Code_0 = 18 THEN 1 ELSE 0 END) AS Dimension_Error,
        SUM(CASE WHEN adiSort_Code_0 = 19 THEN 1 ELSE 0 END) AS Weight_Error,
        SUM(CASE WHEN adiSort_Code_0 = 20 THEN 1 ELSE 0 END) AS Container_Utilization,
        SUM(CASE WHEN adiSort_Code_0 = 21 THEN 1 ELSE 0 END) AS Unable_To_Divert,
        SUM(CASE WHEN adiSort_Code_0 = 22 THEN 1 ELSE 0 END) AS Destination_Not_Attempted
    FROM item_data
    WHERE t_stamp BETWEEN :starttime AND :endtime
        AND adiSort_Code_0 NOT IN (11, 15)
        AND (COALESCE(:Lane, '') = '' OR sActual_Dest_ID = :Lane)
    GROUP BY roundtime, sActual_Dest_ID
    ORDER BY roundtime ASC
) basa;