WITH pre AS(
SELECT
    I.ITEM_CODE AS CODE,
    I.ITEM_NAME AS NAME,
    I.USER_UPDATE_DATETIME AS UPD,
    IH.ITEM_CODE AS CODE_H,
    IH.ITEM_NAME AS NAME_H,
    IH.USER_UPDATE_DATETIME AS UPD_H
FROM
    ITEM AS I
FULL JOIN
    ITEM_HISTORY AS IH
ON
    I.ITEM_CODE = IH.ITEM_CODE
WHERE
    UPD != UPD_H
OR
    CODE_H IS NULL
OR
    CODE IS NULL
)
SELECT
    COALESCE(CODE, CODE_H) AS CODE,
    COALESCE(NAME, NAME_H) AS NAME,
    CASE 
        WHEN CODE IS NULL THEN 'DELETED'
        WHEN CODE_H IS NULL THEN 'ADDED'
        WHEN UPD != UPD_H THEN 'UPDATED'
    END AS COMP_RSLT
FROM
    pre
ORDER BY
    CODE DESC