Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Variable select query Expand / Collapse
Author
Message
Posted Friday, October 18, 2013 12:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 21, 2014 8:35 PM
Points: 6, Visits: 25
Hello everybody.

I am a researcher looking for answers.

I have a portfolio of select queries, and am looking for a simple (?) modification.

use table
Go

Select *
from table_name
where
condition 1
condition 2
"
"
""""""""
condition 10

This only returns rows where all 10 conditions are true. Is there a simple way to return all rows where 8 or more of the where statements are true?


Post #1506284
Posted Friday, October 18, 2013 12:26 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:36 PM
Points: 13,111, Visits: 11,946
Pretty sparse on details here but I think you could do something like this. I'm not so sure about the "simple" part but it should work.

select * from
(
Select *
, case when condition1 then 1 else 0 end
+ case when condition2 then 1 else 0 end
+ case when condition3 then 1 else 0 end as ConditionCount
from table_name
where
condition 1
OR condition 2
) x
where ConditionCount >= 8

This is is a rather strange scenario.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1506290
Posted Friday, October 18, 2013 12:39 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:12 PM
Points: 1,342, Visits: 1,514
I tested a similar solution successfully...

SELECT *
FROM table
WHERE (
CASE
WHEN condition1 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition2 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition3 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition4 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition5 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition6 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition7 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition8 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition9 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition10 = 'xxx'
THEN 1
ELSE 0
END
) >= 8

Post #1506297
Posted Friday, October 18, 2013 12:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 21, 2014 8:35 PM
Points: 6, Visits: 25
Thanks Sean. I'll test it later when I get home. I was trying to use a variable, but I can see how this would work.
Post #1506303
Posted Friday, October 18, 2013 12:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 21, 2014 8:35 PM
Points: 6, Visits: 25
Thanks Batgirl.

(I've always wanted to say that!)

I'll check this later.
Post #1506304
Posted Friday, October 18, 2013 12:53 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:41 AM
Points: 128, Visits: 820
Nice! It seems like a strange request but I get it often. The boss generally wants a few exact match clauses and a subset of several "fuzzy match" clauses to determine what gets combined when I import awful duplicate customer data.

Last week I had a tougher scenario of joining about an average of a dozen rows into one (from thousands) where they matched each other on various subsets of many clauses.
Post #1506305
Posted Friday, October 18, 2013 1:10 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:36 PM
Points: 13,111, Visits: 11,946
kennethrbell (10/18/2013)
Thanks Sean. I'll test it later when I get home. I was trying to use a variable, but I can see how this would work.


You're welcome. Please post back with an update when you get a chance.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1506312
Posted Friday, October 18, 2013 2:14 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:54 PM
Points: 952, Visits: 2,890
Bill Talada (10/18/2013)
Nice! It seems like a strange request but I get it often. The boss generally wants a few exact match clauses and a subset of several "fuzzy match" clauses to determine what gets combined when I import awful duplicate customer data.

Last week I had a tougher scenario of joining about an average of a dozen rows into one (from thousands) where they matched each other on various subsets of many clauses.
We do a similar query where there are multiple columns to test a match against and those matches are weighted
case when col1 = val1 then 30 when col1 = other1 then 20 else 0 end +
case when col2 = val2 then 5 else 0 end +
...

However I've found that using this in a where clause can really make the query plan stink
Post #1506329
Posted Friday, October 18, 2013 6:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 21, 2014 8:35 PM
Points: 6, Visits: 25
Thanks everybody. I'd been trying to figure this out for a while. I was using local variables with a case function which apparently don't work in a select statement. I even posed the question to a local SQL users group that meets on Microsoft campus, they thought it was an odd question too. Aah, the life of datamining.

I was able to get Batgirl's query to work. I love the linear regression idea, too. Maybe that will be the next version. I need to figure what values to assign!

Sean, I tried yours but got lost somewhere. Probably because I have several conditions (i.e. where clauses) that I don't want in the count but are universal. I figure if I filter by those first it will speed up the query.

So here is my slight modification of Batgirls query.

SELECT *
FROM table
WHERE
--these UniversalConditions (where clauses) are mandatory all must be true
UniversalCondition1
UniversalCondition2
UniversalCondition3
UniversalCondition4
--inside the Case Statement are Variable Conditions where I will accept 8
--out of 10
(
CASE
WHEN condition1 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition2 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition3 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition4 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition5 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition6 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition7 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition8 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition9 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition10 = 'xxx'
THEN 1
ELSE 0
END
) >= 8


Is there always one more question?

Mine is, "is there a way to have the total count display as well?" In other words, is this row a "10" a "9" or an "8"?
Post #1506349
Posted Monday, October 21, 2013 7:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:36 PM
Points: 13,111, Visits: 11,946
kennethrbell (10/18/2013)

Is there always one more question?

Mine is, "is there a way to have the total count display as well?" In other words, is this row a "10" a "9" or an "8"?


Adding the count to the output is what the code I posted does.

It really isn't too complicated. I just put the case expression as a column instead of in the where clause then turned the entire query into a subquery. That allows to then filter the query and/or display the count.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1506674
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse