Calculating Price Differances

[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

[Former Member · Accepted solution]

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