Skip to Content
0

Calculating Price Differances

Mar 25 at 10:19 AM

56

avatar image
Former Member

I need to run a column to calculate the difference between two price lists.

My query so far displays correctly but calculating these from different tables is above my experience level.

I need to calculate the difference between pricelist 1 and 5 in a new column and again between 2 and 3

My query so far only displays:

SELECT ITM1.ItemCode,
OITM.ItemName,
OITM.U_PRX_WbAv,
OITM.ItmsGrpCod,
OITM.OnHand,
OITM.QryGroup11 as 'H1 Price Group'
,MAX(price1) as 'Actual Cost'
,MAX(price5) as 'AMG Dealer'
,MAX(price2) as 'AMG Retail'
,MAX(price3) as 'Mil Wholesale'
FROM
(
SELECT ItemCode
,CASE WHEN PriceList = 1 THEN price END AS Price1
,CASE WHEN PriceList = 5 THEN price END AS Price5
,CASE WHEN PriceList = 2 THEN price END AS Price2
,CASE WHEN PriceList = 3 THEN price END AS Price3
FROM ITM1
) AS ITM1
JOIN OITM ON ITM1.ItemCode = OITM.ItemCode
where OITM.SuppCatNum = 'H1'
and not MAX(price1) = MAX(price5)

GROUP BY ITM1.ItemCode,
OITM.ItemName,
OITM.U_PRX_WbAv,
OITM.ItmsGrpCod,
OITM.OnHand,
OITM.QryGroup11

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
avatar image
Former Member Mar 28 at 04:14 PM
0

figured it out... stupid simple actually...

OITM.OnHand
,MAX(price5) as 'AMG Dealer'
,MAX(price1) as 'Actual Cost'
,(MAX(price1))-(MAX(price5)) as 'Cost Diff.'
,MAX(price2) as 'AMG Retail'
,MAX(price3) as 'Mil Wholesale'
,(MAX(price3))-(MAX(price2)) as 'Price Diff.'
FROM

Share
10 |10000 characters needed characters left characters exceeded