Join one from multiple possibilities

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Join one from multiple possibilities

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-01-12 : 12:34:50

I have a TRX table that contains:

Vendor
ItemNo
Quantity

I have a PRICE table that contains:
Vendor
ItemNo
MinQty
Price

There can be multiple prices for each Vendor and ItemNo for the Quantity that is purchased based on MinQty. So, there could be a price for a Quantity of 500, 200, 100, 50 etc. I need to get the correct price.

Is this the best way to accomplish this?...

SELECT TRX.Vendor, TRX.Item, (Select TOP 1 Price FROM PRICE Where Price.Vendor = TRX.Vendor AND Price.ItemNo = TRX.ItemNo AND Price.MinQty <= TRX.Quantity ORDER BY Price.MinQty DESCENDING)
AS Price FROM TRX

Thanks in advance.

KTB

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-12 : 12:42:01

What do you mean the correct price? How do you know which one is the correct price? In your example, what would the answer be?

Tara

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2004-01-12 : 12:46:12

Ken-
You need to loop through the table in descending quantity order, adding price to a total and decrementing quantity until you've priced the order.

example
700 widgets

--widget price table--
500 $6.50 (all per item)
100 $7.00
50 $8.00
1 $9.50

so you loop through the price table
@ 500, 1 --> $6.50*500, 200 remain
@ 100, 2 --> $7.00*200, 0 remain *done*
---------
$4,650

Jonathan
codesmith templates

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-01-12 : 12:54:54

OK Tara, here's a sample price table...

Vendor ItemNo Price MinQty
1 122 $100 500
1 122 $200 200
1 122 $300 100
1 122 $450 50
1 122 $600 1

If TRX.Quantity is 152, I need the price for 100 items of $300.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-12 : 13:03:50

SELECT MIN(p.Price)
FROM Price p
INNER JOIN TRX t ON p.Vendor = t.Vendor AND p.ItemNo = t.ItemNo
WHERE p.MinQty < t.Quantity

Tara

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-01-12 : 13:16:52

Thanks Tara, but I think you mean MAX(p.Price)

So that's better than my TOP 1? Is MIN()/MAX() generally better performance wise than TOP x? Also is a JOIN usually better that using WHERE conditions to join 2 tables?

Just wunderin as usual.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-12 : 13:22:43

MAX doesn't work. See this code:



SET NOCOUNT ON

CREATE TABLE Price
(

Vendor INT,
ItemNo INT,
Price INT,
MinQty INT)

CREATE TABLE TRX
(
Vendor INT,
ItemNo INT,
Quantity INT
)

INSERT INTO Price VALUES(1,122, 100, 500)
INSERT INTO Price VALUES(1,122, 200, 200)
INSERT INTO Price VALUES(1,122, 300, 100)
INSERT INTO Price VALUES(1,122, 450, 50)
INSERT INTO Price VALUES(1,122, 600, 1)

INSERT INTO TRX VALUES(1, 122, 152)

SELECT MIN(p.Price)
FROM Price p 
INNER JOIN TRX t ON p.Vendor = t.Vendor AND p.ItemNo = t.ItemNo
WHERE p.MinQty < t.Quantity

DROP TABLE Price
DROP TABLE TRX

If I used MAX, I would get $600 instead of $300.

My version is slightly better than yours. If you take a look at the execution plan for both in one window, you'll notice mine uses 48.32$ of the batch and yours uses 51.68%.

I prefer the JOIN syntax as it is SQL-92 syntax.

Tara

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-01-12 : 13:25:55

OK, me too! Thanks alot again Tara.

glen34
Starting Member

2 Posts

Posted - 2004-02-04 : 14:31:19

This one helped. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources