| Author |
 Topic  |
|
|
SergioM
Yak Posting Veteran
72 Posts |
 Posted - 05/15/2012 : 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... |
Edited by - SergioM on 05/15/2012 14:19:22
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7007 Posts |
Posted - 05/15/2012 : 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
India
48061 Posts |
Posted - 05/15/2012 : 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
Sweden
29156 Posts |
Posted - 05/16/2012 : 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)
USA
7007 Posts |
Posted - 05/16/2012 : 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
Sweden
29156 Posts |
Posted - 05/17/2012 : 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
USA
3 Posts |
Posted - 05/25/2012 : 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
India
48061 Posts |
Posted - 05/25/2012 : 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/
|
|
|
| |
Topic |
|
How do I Select where 2/10 combinations are true
