2022 Nov 23 2:19 PM
Good afternoon,
I'm trying to make a report that shows all tier prices for a certain product with each tier amount in a new column.
So column 1 = Product, Column 2 = price for 1 piece, Column 3 = price for 5 pieces, etc.
The problem however is that I only want prices from pricelist 1.
The following code unfortunately also includes the other pricelists.
SELECT T0.[ItemCode],
(SELECT SUM([Price]) FROM SPP2 WHERE T2.[PriceList] = 1 AND [Amount] = 1 AND itemcode = t0.itemcode) AS '1',
(SELECT SUM([Price]) FROM SPP2 WHERE T2.[PriceList] = 1 AND [Amount] = 5 AND itemcode = t0.itemcode) AS '5',
(SELECT SUM([Price]) FROM SPP2 WHERE T2.[PriceList] = 1 AND [Amount] = 10 AND itemcode = t0.itemcode) AS '10',
(SELECT SUM([Price]) FROM SPP2 WHERE T2.[PriceList] = 1 AND [Amount] = 25 AND itemcode = t0.itemcode) AS '25',
(SELECT SUM([Price]) FROM SPP2 WHERE T2.[PriceList] = 1 AND [Amount] = 50 AND itemcode = t0.itemcode) AS '50'
FROM SPP2 T0 INNER JOIN ITM1 T2 ON T0.[ItemCode] = T2.[ItemCode] WHERE T2.[PriceList] = 1
Group By T0.[ItemCode], T2.[PriceList]
I also tried to join the table like this, but it gets the same result:
(SELECT SUM(T4.[Price]) FROM SPP2 T4 INNER JOIN ITM1 T2 ON T4.[ItemCode] = T2.[ItemCode] WHERE T2.[PriceList] = 1 AND T4.[Amount] = 1 AND T4.itemcode = t0.itemcode) AS '1',