Multiple Prices Query - 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 2005 Forums

Transact-SQL (2005)

Multiple Prices Query

Author

Topic

r2dhart
Starting Member

3 Posts

Posted - 2010-09-16 : 11:25:58

I have a poorly designed table containing product information which includes five price columns (Price1, Price2, etc.). So I need to query all the ID's for products that have a non-null value in two or more of the "Price" columns. In addition I need to make sure the values are not duplicates.

Here's the beginning but I'm having trouble with the WHERE part of my clause.

SELECT [ID], [Price1], [Price2], [Price3], [Price4], [Price5]
FROM Products
WHERE ?

Any help would be greatly appreciated.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-09-16 : 11:38:53

Try this -


SELECT 
* FROM 
(
SELECT ID, 
CASE WHEN price1 IS NOT NULL THEN 1 ELSE 0 END + 
CASE WHEN price1 IS NOT NULL THEN 1 ELSE 0 END +  
CASE WHEN price1 IS NOT NULL THEN 1 ELSE 0 END +  
CASE WHEN price1 IS NOT NULL THEN 1 ELSE 0 END +  
CASE WHEN price1 IS NOT NULL THEN 1 ELSE 0 END AS Price
FROM products
) A WHERE Price > 2

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

r2dhart
Starting Member

3 Posts

Posted - 2010-09-16 : 12:30:38

After a slight correction that worked like a charm. Thank you.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-09-17 : 03:06:23

Welcome

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources