Find Order Ship To Mismatch

select b.custnmbr as [Customer Number], a.sopnumbe as [SOP Number], itemnmbr as [Item Number], b.prstadcd as Order_ShipTo, 
a.prstadcd as Item_ShipTo, c.prstadcd as Customer_ShipTo, 'Open' as Status
from sop10200 a join sop10100 b on a.sopnumbe = b.sopnumbe
join rm00101 c on b.custnmbr = c.custnmbr
where a.prstadcd != c.prstadcd

union

select b.custnmbr as [Customer Number], a.sopnumbe as [SOP Number], itemnmbr as [Item Number], b.prstadcd as Order_ShipTo, 
a.prstadcd as Item_ShipTo, c.prstadcd as Customer_ShipTo, 'History' as Status
from sop30300 a join sop30200 b on a.sopnumbe = b.sopnumbe
join rm00101 c on b.custnmbr = c.custnmbr
where a.prstadcd != c.prstadcd
order by status desc, b.custnmbr, a.sopnumbe, itemnmbr

Leave a Reply