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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
ralmadaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.