Skip to Content
avatar image
Former Member

Calculating Price Differances

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

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

    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

    Add comment
    10|10000 characters needed characters exceeded