Skip to Content
0
Former Member
Mar 25, 2018 at 10:19 AM

Calculating Price Differances

135 Views Last edit May 11, 2018 at 09:13 PM 2 rev

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