Detect price difference for an item

Jonathan Brotto 1,076 Reputation points

I exported a query to excel but before I do it, I was wondering if I use a partition by and something like rank to create a column that indicates that all items are not the same price.


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,050 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,575 questions
0 comments No comments
{count} votes

Accepted answer
  1. Naomi 7,361 Reputation points


    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   
     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]'
    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Naomi 7,361 Reputation points

    You can use condition
    case when max(price) over (partition by item) <> min(price) over (partition by Item) then 'All items are not the same price' else 'All items are the same price' end

  2. Ronen Ariely 15,101 Reputation points


    (1) We cannot query image!

    Please provide queries to create the table and insert the sample data

    (2) The description is not clear to me. Please go over the points I mention in the following image and clarify

    Note! the first question should be: why these are NOT the different

  3. Bert Zhou-msft 3,421 Reputation points

    Hi,@Jonathan Brotto

    Welcome to Microsoft T-SQL Q&A Forum!

    Here I agree with NaomiNNN's approach.
    I have tested it locally, and I can find different columns after grouping. As a reminder, here you need to sort the tity column. The following are my test results:

     ;with Ranked  
    	select *,  
    	Compare_Result=case when  
    	max(P_After_Discount) over (partition by Part_Number order by tity ) <> min(P_After_Discount) over (partition by Part_Number order by tity)   
    	then 'All items are not the same price'   
    	else 'All items are the same price'   
    	from ##test  
     * from Ranked  


    Note: If it does not meet your expectations, please provide the following sample statement, because my test only created some columns to achieve the results I expected.

    Best regards,
    Bert Zhou

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.