SELECT
    t_stamp AS `Timestamp`,
    'S01' AS `Sorter`,
    sLocation_ID AS `Scanner`,
    id AS `Parcel ID`,
    sBarcode AS `Scan Label`,

    CASE 
        WHEN adiSort_Code_0 = 8 THEN 'No_Read'
        WHEN adiSort_Code_0 = 9 THEN 'No_Code'
        WHEN adiSort_Code_0 = 10 THEN 'Multi_Label'
        ELSE 'Good Read'
    END AS `Scan Status`,

    iPackage_Length AS `Item Length`,

    asRequested_Dest_ID_0 AS `Requested Destination`,
    sActual_Dest_ID AS `Actual Destination`,

    -- ALL CAPS, SPACES
    CASE adiSort_Code_0
        WHEN 0  THEN 'SUCCESS'
        WHEN 1  THEN 'UNKNOWN'
        WHEN 2  THEN 'UNEXPECTED CONTAINER'
        WHEN 3  THEN 'TRACKING ERROR'
        WHEN 4  THEN 'GAP ERROR'
        WHEN 5  THEN 'DESTINATION FULL'
        WHEN 6  THEN 'DESTINATION NON OPERATIONAL'
        WHEN 7  THEN 'INVALID DESTINATION'
        WHEN 8  THEN 'NO READ'
        WHEN 9  THEN 'NO CODE'
        WHEN 10 THEN 'MULTI LABEL'
        WHEN 11 THEN 'RESERVED'
        WHEN 12 THEN 'DESTINATION DISABLED'
        WHEN 13 THEN 'THROUGHPUT LIMIT'
        WHEN 14 THEN 'FAILED TO DIVERT'
        WHEN 15 THEN 'RESERVED'
        WHEN 16 THEN 'NO DESTINATION RECEIVED'
        WHEN 17 THEN 'LOST CONTAINER'
        WHEN 18 THEN 'DIMENSION ERROR'
        WHEN 19 THEN 'WEIGHT ERROR'
        WHEN 20 THEN 'CONTAINER UTILIZATION'
        WHEN 21 THEN 'UNABLE TO DIVERT'
        WHEN 22 THEN 'DESTINATION NOT ATTEMPTED'
        ELSE 'UNKNOWN'
    END AS `Reason Code`,

    adiSort_Code_0 AS `Reason Code Numeric`

FROM item_data
WHERE t_stamp BETWEEN :starttime AND :endtime


UNION ALL

SELECT
    :starttime AS `Timestamp`,
    'S01' AS `Sorter`,
    'N/A' AS `Scanner`,
    0 AS `Parcel ID`,
    'N/A' AS `Scan Label`,
    'N/A' AS `Scan Status`,
    0 AS `Item Length`,
    'N/A' AS `Requested Destination`,
    'N/A' AS `Actual Destination`,
    'No Data' AS `Reason Code`,
    0 AS `Reason Code Numeric`
WHERE NOT EXISTS (
    SELECT 1 FROM item_data 
    WHERE t_stamp BETWEEN :starttime AND :endtime
)

ORDER BY `Timestamp`;