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

Processing Queries via Email

In SQL 2000, we processed queries and return results to user. In SQL 2005, we
have not been successful in doing so. I do not see any way using DB Mail to
process incoming mail.
We have set up SQL Mail and are able to send mail with no problem. We had
planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
we could migrate to whatever process would provide the same functionality.
So, two questions:
1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
queries and returning results to user?
2. What provides the equivalent functionality in SQL 2005? Is it done via
Reporting Service?
If someone can point me in the right direction for either question, I would
be very grateful.
Hi Roger
AFAIK there is no method of reading a mailbox with database mail, therefore
you would have to retain your current process of reading the mailbox or
possibly use the CLR to do this (for example see
http://support.microsoft.com/kb/813349 ). You could use a different (possibly
more reliable) method of delivery such as a web service instead which can be
hosted by the database server.
John
"RogerT" wrote:

> In SQL 2000, we processed queries and return results to user. In SQL 2005, we
> have not been successful in doing so. I do not see any way using DB Mail to
> process incoming mail.
> We have set up SQL Mail and are able to send mail with no problem. We had
> planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
> we could migrate to whatever process would provide the same functionality.
> So, two questions:
> 1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
> queries and returning results to user?
> 2. What provides the equivalent functionality in SQL 2005? Is it done via
> Reporting Service?
> If someone can point me in the right direction for either question, I would
> be very grateful.

Processing Queries via Email

In SQL 2000, we processed queries and return results to user. In SQL 2005, w
e
have not been successful in doing so. I do not see any way using DB Mail to
process incoming mail.
We have set up SQL Mail and are able to send mail with no problem. We had
planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
we could migrate to whatever process would provide the same functionality.
So, two questions:
1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
queries and returning results to user?
2. What provides the equivalent functionality in SQL 2005? Is it done via
Reporting Service?
If someone can point me in the right direction for either question, I would
be very grateful.Hi Roger
AFAIK there is no method of reading a mailbox with database mail, therefore
you would have to retain your current process of reading the mailbox or
possibly use the CLR to do this (for example see
http://support.microsoft.com/kb/813349 ). You could use a different (possibl
y
more reliable) method of delivery such as a web service instead which can be
hosted by the database server.
John
"RogerT" wrote:

> In SQL 2000, we processed queries and return results to user. In SQL 2005,
we
> have not been successful in doing so. I do not see any way using DB Mail t
o
> process incoming mail.
> We have set up SQL Mail and are able to send mail with no problem. We had
> planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail unti
l
> we could migrate to whatever process would provide the same functionality.
> So, two questions:
> 1. Has anyone been successful using SQL Mail on SQL 2005 to process incomi
ng
> queries and returning results to user?
> 2. What provides the equivalent functionality in SQL 2005? Is it done via
> Reporting Service?
> If someone can point me in the right direction for either question, I woul
d
> be very grateful.

Processing Queries via Email

In SQL 2000, we processed queries and return results to user. In SQL 2005, we
have not been successful in doing so. I do not see any way using DB Mail to
process incoming mail.
We have set up SQL Mail and are able to send mail with no problem. We had
planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
we could migrate to whatever process would provide the same functionality.
So, two questions:
1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
queries and returning results to user?
2. What provides the equivalent functionality in SQL 2005? Is it done via
Reporting Service?
If someone can point me in the right direction for either question, I would
be very grateful.Hi Roger
AFAIK there is no method of reading a mailbox with database mail, therefore
you would have to retain your current process of reading the mailbox or
possibly use the CLR to do this (for example see
http://support.microsoft.com/kb/813349 ). You could use a different (possibly
more reliable) method of delivery such as a web service instead which can be
hosted by the database server.
John
"RogerT" wrote:
> In SQL 2000, we processed queries and return results to user. In SQL 2005, we
> have not been successful in doing so. I do not see any way using DB Mail to
> process incoming mail.
> We have set up SQL Mail and are able to send mail with no problem. We had
> planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
> we could migrate to whatever process would provide the same functionality.
> So, two questions:
> 1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
> queries and returning results to user?
> 2. What provides the equivalent functionality in SQL 2005? Is it done via
> Reporting Service?
> If someone can point me in the right direction for either question, I would
> be very grateful.

procedure to retrieve primary key

Hello!
I want to write a stored procedure, that will insert new row into a table and return the key of newly inserted row.check out SCOPE_IDENTITY() in bol

EDIT: i am assuming you are using an identity column for your pk. if you aren't, then you must already know the value you are inserting, right?|||That's exactly what I was looking for. Thanks.