WITH COMP AS (
SELECT i.ITEM_CODE AS CODE, i.ITEM_NAME AS NAME, 'ADDED' AS COMP_RSLT
FROM ITEM AS i LEFT OUTER JOIN ITEM_HISTORY AS ih ON i.ITEM_CODE = ih.ITEM_CODE
WHERE DATE(i.USER_UPDATE_DATETIME) BETWEEN '2023-12-31' AND '2024-04-01' AND ih.ITEM_CODE IS NULL
UNION
SELECT i.ITEM_CODE AS CODE, i.ITEM_NAME AS NAME, 'UPDATED' AS COMP_RSLT
FROM ITEM AS i LEFT OUTER JOIN ITEM_HISTORY AS ih ON i.ITEM_CODE = ih.ITEM_CODE
WHERE DATE(i.USER_UPDATE_DATETIME) BETWEEN '2023-12-31' AND ih.ITEM_CODE IS NOT NULL AND i.USER_UPDATE_DATETIME != ih.USER_UPDATE_DATETIME
UNION
SELECT ih.ITEM_CODE AS CODE, ih.ITEM_NAME AS NAME, 'DELETED' AS COMP_RSLT
FROM ITEM_HISTORY AS ih LEFT OUTER JOIN ITEM AS i ON i.ITEM_CODE = ih.ITEM_CODE
WHERE DATE(ih.USER_UPDATE_DATETIME) BETWEEN '2023-12-31' AND '2024-04-01' AND i.ITEM_CODE IS NULL
)
SELECT * FROM COMP ORDER BY CODE DESC;