How do I Select where 2/10 combinations are true

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

Transact-SQL (2008)

How do I Select where 2/10 combinations are true

Author

Topic

SergioM
Posting Yak Master

170 Posts

Posted - 2012-05-15 : 14:18:31

I have a table which holds the product information for various vendors. Let's say I have 10 vendors. Sometimes, only one vendor has X product, so it's easy to see who I will buy from. But sometimes, several vendors offer the same item. How do I write a query that checks against all 10 where only 2/10 need to be greater than 0?

I could write it this way, but it is really silly...

WHERE
(Dist1_Qty>0 AND (Dist2_Qty>0 OR Dist3_Qty>0 OR Dist4_Qty>0 OR Dist5_Qty>0 OR Dist6_Qty>0 OR Dist7_Qty>0 OR Dist8_Qty>0 OR Dist9_Qty>0 OR Dist10_Qty>0))
OR
(Dist2_Qty>0 AND (Dist1_Qty>0 OR Dist3_Qty>0 OR Dist4_Qty>0 OR Dist5_Qty>0 OR Dist6_Qty>0 OR Dist7_Qty>0 OR Dist8_Qty>0 OR Dist9_Qty>0 OR Dist10_Qty>0))
OR
(Dist3_Qty>0 AND (Dist1_Qty>0 OR Dist2_Qty>0 OR Dist4_Qty>0 OR Dist5_Qty>0 OR Dist6_Qty>0 OR Dist7_Qty>0 OR Dist8_Qty>0 OR Dist9_Qty>0 OR Dist10_Qty>0))
OR
ETC...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-05-15 : 14:40:50

Assuming that what you really want is when at least 2 of the 10 columns are > 0, then this will do it.

WHERE
	2 <=
	case when Dist1_Qty>0 then 1 else 0 end+
	case when Dist2_Qty>0 then 1 else 0 end+
	case when Dist3_Qty>0 then 1 else 0 end+
	case when Dist4_Qty>0 then 1 else 0 end+
	case when Dist5_Qty>0 then 1 else 0 end+
	case when Dist6_Qty>0 then 1 else 0 end+
	case when Dist7_Qty>0 then 1 else 0 end+
	case when Dist8_Qty>0 then 1 else 0 end+
	case when Dist9_Qty>0 then 1 else 0 end+
	case when Dist10_Qty>0 then 1 else 0 end

Of course, this is a good illustration of why you would want to normalize the data so that there is one row per product vendor combination. Then your query becomes fairly trivial.

CODO ERGO SUM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-15 : 22:29:16

another way is to use unpivot and check


SELECT Vendor
FROM table t
UNPIVOT (Qty FOR Cat IN (Dist1_Qty,Dist1_Qty,Dist1_Qty,...,Dist1_Qty))u
GROUP BY Vendor
HAVING SUM(CASE WHEN Qty>0 THEN 1 ELSE 0 END) >2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-16 : 00:41:23

WHERE SIGN(Dist1_Qty) + SIGN(Dist2_Qty) + SIGN(Dist3_Qty) + SIGN(Dist4_Qty) + SIGN(Dist5_Qty) + SIGN(Dist6_Qty) + SIGN(Dist7_Qty) + SIGN(Dist8_Qty) + SIGN(Dist9_Qty) + SIGN(Dist10_Qty) >= 2

N 56°04'39.26"
E 12°55'05.63"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-05-16 : 23:38:02

quote:
Originally posted by SwePeso

WHERE SIGN(Dist1_Qty) + SIGN(Dist2_Qty) + SIGN(Dist3_Qty) + SIGN(Dist4_Qty) + SIGN(Dist5_Qty) + SIGN(Dist6_Qty) + SIGN(Dist7_Qty) + SIGN(Dist8_Qty) + SIGN(Dist9_Qty) + SIGN(Dist10_Qty) >= 2

N 56°04'39.26"
E 12°55'05.63"

If those columns are nullable, then a null value would return null for the whole expression.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-17 : 03:46:03

Sure. And negative values would screw the solution too.

N 56°04'39.26"
E 12°55'05.63"

ivycool
Starting Member

3 Posts

Posted - 2012-05-25 : 00:05:32

this is a good illustration of why you would want to normalize the data so that there is one row per product vendor combination

unspammed

Bree Vandicamp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-25 : 10:11:12

quote:
Originally posted by ivycool

this is a good illustration of why you would want to normalize the data so that there is one row per product vendor combination

unspammed

Bree Vandicamp

yep...exactly
else you need to have similar manipulations before you want to do any comparison on them

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources