Product Price Range Query

I've noticed that some search queries on e-commerce sites return "item
price range hyperlinks" as filters for matched items.
So you would have something like:
Under $50 $75-$100 $150-$250
in a row with only the applicable price ranges to the queried products
showing. So, with the above example, if there weren't any products
between $101 and $149, that price range wouldn't show.
I'm wondering about the structure of the SQL for this type of query.
On the surface, I could do multiple queries for each of the price
ranges I needed and union them together, but I'm wondering if there is
a more efficient way to do it in one pass.
Ian<dontspammenow@.yahoo.com> wrote in message
news:1132866827.910562.120930@.g43g2000cwa.googlegroups.com...
> I've noticed that some search queries on e-commerce sites return "item
> price range hyperlinks" as filters for matched items.
> So you would have something like:
> Under $50 $75-$100 $150-$250
> in a row with only the applicable price ranges to the queried products
> showing. So, with the above example, if there weren't any products
> between $101 and $149, that price range wouldn't show.
> I'm wondering about the structure of the SQL for this type of query.
> On the surface, I could do multiple queries for each of the price
> ranges I needed and union them together, but I'm wondering if there is
> a more efficient way to do it in one pass.
> Ian
>
Put your possible price ranges in a table:
CREATE TABLE price_ranges (low_price DECIMAL(10,2) NOT NULL, high_price
DECIMAL (10,2) NOT NULL, CHECK (low_price < high_price), PRIMARY KEY
(low_price, high_price));
INSERT INTO price_ranges (low_price, high_price)
SELECT 1, 50 UNION ALL
SELECT 50, 75 UNION ALL
SELECT 75, 100 UNION ALL
SELECT 100, 150 UNION ALL
SELECT 150, 250 UNION ALL
SELECT 250, 99999999 ;
Now try one of these queries:
SELECT R.low_price, R.high_price
FROM northwind.dbo.products AS P,
price_ranges AS R
WHERE P.unitprice >= R.low_price
AND P.unitprice < R.high_price
/* AND ... ? */
GROUP BY R.low_price, R.high_price ;
SELECT R.low_price, R.high_price
FROM price_ranges AS R
WHERE EXISTS
(SELECT *
FROM northwind.dbo.products AS P
WHERE P.unitprice >= R.low_price
AND P.unitprice < R.high_price
/* AND ... ? */) ;
David Portas
SQL Server MVP
--

Product Dimensions

I have several items and the business views these items according to a number of attributes linked to the item:
- Product category
- Product source (manufactured, purchased, transferred)
- Brand
- Flavour
- Packing Group

The Packing Group is a combination of attributes which defines the size of a pack (bottle size, cap type, things like that). There is never a set hierarchy as to how these are viewed. I've thought about modelling items as:
1. Create a star-schema dimension which has Product category then Product, and have a bunch of attributes hanging off Product
2. Create virtual dimensions based on these attributes

However, this seems problematic to me as the Packing Group has its own attributes. I then consider I might need a "physical" star-schema dimension for each of the above classifications. Is this the right way to go? Can any body suggest a better way or point me to some examples? I'm running AS 2000.

Upgrade to 2005

The last time I did this in AS 2000, I was representing customer demographics - race, gender, age range, income range, etc), I created a physical dimension table in my star to represent each unique combination of attributes. then I created an AS dimension for each attribute. Performance was pretty poor.