Selecting correct price band - SQL Server Forums

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)

Selecting correct price band

Author

Topic

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2003-10-25 : 15:57:33

I have a table of products and a table of price bands.

e.g. 1 = $3, 2-4 = $2, 5+ = $1

A price band row has a lowerBound, price and product ID.

I need to select the correct price band for a set of products each of which have a quantity - which come from a shopping cart.


SELECT
	p.productID, p.prodname, pb.lowerBound, pb.price, c.quantity

FROM 
	ShoppingCart c

INNER JOIN 
	products p ON p.productID = c.productID AND p.priceStyle = 2

INNER JOIN 
	productpricebands pb on pb.productID= p.productID and c.quantity >= pb.lowerbound

WHERE 
	cartID = Cast(21 As varchar(50))

This code selects all the products from a given shopping cart which are priced using price bands. It filters out all of the price bands which are too high but it doesn't get rid of the price bands which are too low.

This is a sample result set...


id name     lowerbound   price  quantity
11 product1	1	2.0000	8	
11 product1	2	3.0000	8	
11 product1	6	4.0000	8	
12 product2	1	2.0000	1

You can see that the lowerbounds of the price bands are all lower than the quantity of the product as specified by the query but how do I get rid of the 2 price bands for 'product1' which shouldn'tbe there.

Cheers,

X-Factor

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-25 : 16:30:00

INNER JOIN
productpricebands pb on pb.productID= p.productID and c.quantity >= pb.lowerbound
adn not exists (select * from productpricebands pb2 where pb.productID= pb2.productID and c.quantity >= pb2.lowerbound and pb2.lowerbound < pb.lowerbound)

or

productpricebands pb on pb.productID= p.productID
and pb.lowerbound = (select min(pb2.lowerbound) from productpricebands pb2 where pb2.productID= pb.productID and
c.quantity >= pb.lowerbound)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2003-10-25 : 18:41:30

Thanks. Got it working with a couple of changes...


pb.productID= p.productID 
and pb.lowerbound = 
	(SELECT
		max(pb2.lowerbound) 
	FROM 
		productpricebands pb2 
	WHERE 
		pb2.productID= pb.productID AND 
		c.quantity >= pb2.lowerbound
	)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources