I'm trying to write a query for an alert that tells me when a price has changed (higher than original) on a purchase order. The query I had come up with is listed below, however, I have an issue where if I create a PO then change the price, it works fine, but if I change the price AGAIN then it will show the original price change and the new price change so there are 2 lines. I only need the original and the latest price change.
Any help or guidance would be greatly appreciated.
SELECT
T0.DocNum [PO Number], T0.DocTotal [Changed Amount], T1.DocTotal [Original Amount]
FROM OPOR T0 inner join ADOC T1 on T0.ObjType = T1.ObjType and T0.DocNum = T1.DocNum
WHERE T0.DocTotal > T1.DocTotal and T0.DocStatus = 'O'