Filter prices based on changes on master price list.

Jonathan Brotto 1,071 Reputation points

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, 
 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   
  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]'
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,598 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,458 questions
{count} votes

Accepted answer
  1. Guoxiong 8,126 Reputation points

    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

0 additional answers

Sort by: Most helpful