SSIS Query - Pivot Records

I have a table, with the following records in question:

Table Name: PriceDiscTable

ItemId      OfferId      Start Date      End Date      QtyAmount      Amount
26357      RT      3/1/11      12/31/99      25      11.49
26357      RT      3/1/11      12/31/99      50      10.99
26357      RT      3/1/11      12/31/99      100      10.49

Would like the following result:

ItemId      OfferId      Start Date      End Date      Quantity 1      Price 1      Quantity 2      Price 2      Quantity 3      Price 3
26357      RT      3/1/11      12/31/99      25      11.49      50      10.99      100      10.49


I tried this the following but, I cannot get the quantity 2 or price 2 record (basically the median of the 2 fields)?:

SELECT     ACCOUNTRELATION AS OFFERID, ITEMRELATION AS ITEMID, SUM((CASE WHEN RELATION = 4 AND QUANTITYAMOUNT = 0 THEN AMOUNT END))
                      AS REGPRICE, CASE WHEN SUM((CASE WHEN RELATION = 5 THEN AMOUNT END)) IS NOT NULL
                      THEN SUM((CASE WHEN RELATION = 5 THEN AMOUNT END)) ELSE 0 END AS DISCOUNT, SUM((CASE WHEN RELATION = 4 AND
                      QUANTITYAMOUNT = 0 THEN AMOUNT END)) - (CASE WHEN SUM((CASE WHEN RELATION = 5 THEN AMOUNT END)) IS NOT NULL
                      THEN SUM((CASE WHEN RELATION = 5 THEN AMOUNT END)) ELSE 0 END) AS SALESPRICE, FROMDATE, TODATE,
                      MIN((CASE WHEN QUANTITYAMOUNT <> 0 THEN QUANTITYAMOUNT END)) AS QTYBREAK1,
                      MAX((CASE WHEN QUANTITYAMOUNT <> 0 THEN QUANTITYAMOUNT END)) AS QTYBREAK3,
                      MAX((CASE WHEN QUANTITYAMOUNT <> 0 THEN AMOUNT END)) AS QTYPRICE1, MIN((CASE WHEN QUANTITYAMOUNT <> 0 THEN AMOUNT END))
                      AS QTYPRICE3
FROM         PRICEDISCTABLE
WHERE     (ACCOUNTRELATION = N'RT') AND (FROMDATE <= GETDATE()) AND (TODATE >= GETDATE()) AND (ITEMRELATION = N'26357')
GROUP BY ACCOUNTRELATION, ITEMRELATION, FROMDATE, TODATE

See attached pic.

Any idea how I can create a SSIS SQL Query to accomplish this? SSIS-Query.jpg
jbiniewskiAsked:
Who is Participating?
 
ralmadaConnect With a Mentor Commented:
Assuming you're in SQL 2005+ , can you try the query in Management studio? Query builder does not support all the t-sql functionalities.
0
 
ralmadaCommented:
try the below
select 	OfferID,
	ItemID,
	FromDate,
	ToDate,
	max(case when rn = 1 then QuantityAmount end) Qty1,
	max(case when rn = 1 then Amount end) Price1,	
	max(case when rn = 2 then QuantityAmount end) Qty2,
	max(case when rn = 2 then Amount end) Price2,	
	max(case when rn = 3 then QuantityAmount end) Qty3,
	max(case when rn = 3 then Amount end) Price3
from (
	SELECT  ACCOUNTRELATION AS OFFERID, 
		ITEMRELATION AS ITEMID, 
		FROMDATE, 
		TODATE, 
		QUANTITYAMOUNT,
		AMOUNT,
		row_number() over (partition by ItemID order by Quantityamount) rn
	FROM PRICEDISCTABLE
) a
group by offerid, itemid, fromdate,todate

Open in new window

0
 
jbiniewskiAuthor Commented:
SSIS Query Builder Error when I try and execute the code you suggested.

Any other ideas? SSIS-Query-Builder-Error.jpg
0
 
jbiniewskiAuthor Commented:
Using Mangement Studio and creating a view and then referencing it in my SSIS package worked.

Thanks.

SELECT     OFFERID, ITEMID, FROMDATE, TODATE, MAX(CASE WHEN RN = 1 THEN QUANTITYAMOUNT END) QTY1, MAX(CASE WHEN RN = 1 THEN AMOUNT END) PRICE1,
                      MAX(CASE WHEN RN = 2 THEN QUANTITYAMOUNT END) QTY2, MAX(CASE WHEN RN = 2 THEN AMOUNT END) PRICE2,
                      MAX(CASE WHEN RN = 3 THEN QUANTITYAMOUNT END) QTY3, MAX(CASE WHEN RN = 3 THEN AMOUNT END) PRICE
FROM         (SELECT     ACCOUNTRELATION AS OFFERID, ITEMRELATION AS ITEMID, FROMDATE, TODATE, QUANTITYAMOUNT, AMOUNT, ROW_NUMBER() OVER (PARTITION BY
                                              ACCOUNTRELATION, ITEMRELATION
                       ORDER BY QUANTITYAMOUNT) rn
FROM         PRICEDISCTABLE
WHERE     ACCOUNTRELATION = 'RT' AND RELATION = 4 AND QUANTITYAMOUNT > 0 AND (FROMDATE <= GETDATE()) AND (TODATE >= GETDATE())) a
GROUP BY OFFERID, ITEMID, FROMDATE, TODATE
0
 
jbiniewskiAuthor Commented:
Great job!

Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.