This works for your sample data.

-- Union both tables together

WITH AllItems (ItemCode, FrgnName, UpdateDate) AS (

SELECT ItemCode, FrgnName, UpdateDate

FROM AITM

UNION ALL

SELECT ItemCode, FrgnName, UpdateDate

FROM OITM

)

-- Number the rows for each itemcode by date order

, AllItemsRanked (ItemCode, FrgnName, UpdateDate, RowNo) AS (

SELECT ItemCode, FrgnName, UpdateDate,

ROW_NUMBER() OVER (PARTITION BY ItemCode ORDER BY UpdateDate)

FROM AllItems

)

-- Self-join to see what's changed

SELECT i1.ItemCode,

i1.FrgnName FromLocation, i2.FrgnName ToLocation,

i2.UpdateDate

FROM AllItemsRanked i1 JOIN AllItemsRanked i2

ON i1.ItemCode = i2.ItemCode

AND COALESCE(i1.FrgnName,'') <> COALESCE(i2.FrgnName,'') -- only want change of location

AND i2.RowNo = i1.RowNo + 1 -- only look at update immediately before

John