Detect price difference for an item

Question

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.

Answer 1

Try:

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 2

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

Answer 3

Hi,

(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

Answer 4

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  
 as  
 (  
	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'   
	end  
	from ##test  
 )  
 select   
 * 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.