Kiplacon - 1 year ago 106
SQL Question

I have a three column table of price breaks called "FPB" that looks like this:

``````[Part Number] [Quantity] [Price]
AAA-AAAA　　　　　100　　　1.23
AAA-AAAA　　　　　200　　　1.15
BBB-BBBB　　　　　100　　　5.60
CCC-CCCC　 　　　 500　　　3.21
....
``````

Where each part number has multiple entries in multiple rows.

I'm trying to reorganize the table to look more like this

``````[Part Number] [Quantity1] [Price 1] [Quantity 2] [Price 2] [Quantity 3....
AAA-AAAA　　　　　　100　　　1.23　　  　200　    　　1.15　　　....
BBB-BBBB　　　　　　100　　　5.60　 　　 ...
CCC-CCCC　　 　　　 500　　　3.21　　　  ...
...
``````

Where each part number has all its entries combined into one row. The first quantity column should have the lowest available quantity, the second should have the second smallest etc. I am trying to do this by first creating a 1-column table with just the unique part numbers using GROUP BY, and then creating more tables for each column that has the information I want in that column, and then joining it by Part Number. The problem comes when calculating the second smallest quantity for each type, done in the second to last section.

``````SELECT PNs.[Part Number], Q1T.Q1, P1T.Price, Q2T.Q2
FROM
(SELECT
[Part Number]
FROM FPB
GROUP BY [Part Number]
) AS PNs,

(SELECT
[Part Number],
MIN(Quantity) AS Q1
FROM FPB
GROUP BY [Part Number]
) AS Q1T,

(SELECT
*
FROM FPB
) AS P1T,

(SELECT
[Part Number],
MIN(IIF(Quantity>Q1T.Q1,Quantity)) AS Q2
FROM FPB
GROUP BY [Part Number]
) AS Q2T

WHERE
PNs.[Part Number] = Q1T.[Part Number]
AND P1T.[Part Number] = PNs.[Part Number]
AND P1T.Quantity = Q1T.Q1
AND Q2T.[Part Number] = PNs.[Part Number]
``````

When I run this query, it asks me to enter a parameter value for Q1T.Q1, even though it already exists. If I remove the code section for Q2T, as well as any references to Q2, it will work without a problem, and it won't ask about a value for the other instances of Q1T.Q1. Why doesn't Q1T.Q1 have a value just for that section, and how can I fix it? As a side note, I'm using the SQL features of a program called PHPRunner, and its client doesn't support UPDATE/DELETE/INSERT/CREATE queries, UNION, and DISTINCT.

You're looking for something like this.

``````select
p1.PartNumber,
ifnull(max(p2.Quantity), 0) + 1 as LowerQuantity,
p1.Quantity as UpperQuantity,
p1.Price,
count(p2.PartNumber) + 1 as PriceTier
from
FPB p1 left outer join FPB p2
on p2.PartNumber = p1.PartNumber and p2.Quantity < p1.Quantity
``````

From there it's easy to pivot in order to insert into a new table:

``````into into NewFPB (PartNumber, Quantity1, Price1, Quantity2, Price2, ...)
select
PartNumber,
min(switch(PriceTier = 1, UpperQuantity)) as Quantity1,
min(switch(PriceTier = 2, UpperQuantity)) as Quantity2, ...
min(switch(PriceTier = 1, Price)) as Price1,
min(switch(PriceTier = 2, Price)) as Price2, ...
from (
select
p1.PartNumber,
ifnull(max(p2.Quantity), 0) + 1 as LowerQuantity,
p1.Quantity as UpperQuantity,
p1.Price,
count(p2.PartNumber) + 1 as PriceTier
from
FPB p1 left outer join FPB p2
on p2.PartNumber = p1.PartNumber and p2.Quantity < p1.Quantity
) data
``````

You might have to tweak it a little bit for Access to accept it. But the core ideas are there.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download