with list as (
    select
        ITEM.ITEM_CODE as CODE,
        ITEM.ITEM_NAME as NAME,
        ITEM.USER_UPDATE_DATETIME as date,
        ITEM_HISTORY.ITEM_CODE as hCODE,
        ITEM_HISTORY.ITEM_NAME as hNAME,
        ITEM_HISTORY.USER_UPDATE_DATETIME as hdate
    from ITEM left join ITEM_HISTORY on ITEM.ITEM_CODE = ITEM_HISTORY.ITEM_CODE
    union
    select
        ITEM.ITEM_CODE as CODE,
        ITEM.ITEM_NAME as NAME,
        ITEM.USER_UPDATE_DATETIME as date,
        ITEM_HISTORY.ITEM_CODE as hCODE,
        ITEM_HISTORY.ITEM_NAME as hNAME,
        ITEM_HISTORY.USER_UPDATE_DATETIME as hdate
    from ITEM_HISTORY left join ITEM on ITEM.ITEM_CODE = ITEM_HISTORY.ITEM_CODE
)
select
    case
        when CODE is not null then CODE
        else hCODE
    end as CODE
    ,
    case
        when NAME is not null then NAME
        else hNAME
    end as NAME,
    case
        when CODE is null AND hCODE is not null then 'DELETED'
        when CODE is not null AND hCODE is null then 'ADDED'
        when date <> hdate then 'UPDATED'
        else null
    end as COMP_RSLT
from list
where COMP_RSLT is not null
order by CODE desc
;