Filter prices based on changes on master price list.

Question

I have this query where it search for when there is a difference in a price for a part item, but now I want a smarter query where it will saw different based on the last price update. The archive table is AITM and master table is OITM. Both have a create and update field.

SELECT T0.DocNum as 'Doc. Number', T0.CardName, T0.NumAtCard as 'Customer PO #', 
  T1.Quantity, T1.ItemCode as 'Part Number', 
  T4.U_PCODE AS 'U_Pcode', T4.U_GACEQ AS 'U_GACEQ', T4.U_Gaceq2018 AS 'U_Gaceq2018', 
  T4.U_COO AS 'COO', T4.U_SignaPCode AS 'P-CODE Sigma', 
  T4.U_BDAF AS 'P-CODE GACEQ', T4.U_GACEQ2 AS '2018-0616-02-01', 
  T4.U_HSK AS 'H-Sick Kids', T5.FirmName,
  T1.Dscription, T1.Codebars, 
  T0.DocDate as 'Delivery Date', T0.CardCode as 'Customer Code', 
  T1.Price as 'Price', T1.Linetotal As Value, 
  T4.SalPackUn,
 case when max(T1.Price) over (partition by T1.ItemCode)<>min(T1.Price) over (partition by T1.ItemCode) then 'Different Prices' else 'Same Price' end as PriceTestOverItem   
  FROM OINV T0  
  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry 
  INNER JOIN OCRD T2 ON T0.CardCOde = T2.CardCode 
  INNER JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode 
  AND T3.GroupType = 'C' 
  INNER JOIN OITM T4 ON T1.ItemCode = T4.ItemCode 
  INNER JOIN OMRC T5 ON T5.FirmCode = T4.FirmCode
  WHERE T1.Quantity != 0
  AND T0.DocDate >='[%1]' AND T0.DocDate <='[%2]' and T3.GroupName = '[%3]'

Answer 1

You may need to LEFT JOIN the following result set which lists the latest updated price by item code to calculate the difference in the price:

SELECT ItemCode, Price
FROM (
    SELECT ItemCode, Price, ROW_NUMBER() OVER(PARTITION BY ItemCode ORDER BY UpdatedDate DESC) AS RN
    FROM AITM 
)
WHERE RN = 1