WITH SUB AS (SELECT I.ITEM_CODE AS ITEM2024,
IH.ITEM_CODE AS ITEM2023,
I.ITEM_NAME AS NAME2024,
IH.ITEM_NAME AS NAME2023,
I.USER_UPDATE_DATETIME AS DATETIME2024,
IH.USER_UPDATE_DATETIME AS DATETIME2023
FROM ITEM I FULL JOIN ITEM_HISTORY IH
ON I.ITEM_CODE = IH.ITEM_CODE)
SELECT ITEM2024 AS CODE,
NAME2024 AS NAME,
'ADDED' AS COMP_RSLT
FROM SUB
WHERE NAME2023 IS NULL
UNION ALL
SELECT ITEM2023 AS CODE,
NAME2023 AS NAME,
'DELETED' AS COMP_RSLT
FROM SUB
WHERE NAME2024 IS NULL
UNION ALL
SELECT ITEM2024 AS CODE,
NAME2024 AS NAME,
'UPDATED' AS COMP_RSLT
FROM SUB
WHERE DATETIME2023 <> DATETIME2024
ORDER BY CODE DESC