Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Tier price query: new column per amount

0 Kudos
116
  • SAP Managed Tags:

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',
0 REPLIES 0