select
CODE
, NAME
, CASE WHEN DELETED = 1 THEN 'DELETED' WHEN UPTIME <> HIS_UPTIME THEN 'UPDATED' ELSE 'ADDED' END as COMP_RSLT
from
(
select
IH.ITEM_CODE as CODE
, IH.ITEM_NAME as NAME
, CASE WHEN I.ITEM_CODE IS NULL THEN 1 else 0 END as DELETED
, I.USER_UPDATE_DATETIME as UPTIME
, IH.USER_UPDATE_DATETIME as HIS_UPTIME
from ITEM_HISTORY as IH
left outer join
ITEM as I
on
I.ITEM_CODE = IH.ITEM_CODE
where
I.USER_UPDATE_DATETIME <> IH.USER_UPDATE_DATETIME OR I.ITEM_CODE IS NULL
union
select
ITEM_CODE as CODE
, ITEM_NAME as NAME
, 0 as DELETED
, USER_UPDATE_DATETIME as UPTIME
, USER_UPDATE_DATETIME as HIS_UPTIME
from ITEM
where ITEM_CODE NOT IN (select ITEM_CODE from ITEM_HISTORY)
)
order by CODE DESC