SQL query to compare two values and choose the "more favorable"

karen1974
karen1974 used Ask the Experts™
on
Hello all,

I'm trying to write a piece of SQL that looks at 2 outcomes -- either "Yes" or "No" -- and assigns the more favorable of the 2 ("Yes") when a memberID comes up multiple times in a search.

I think it should be straightforward and I checked solutions but here is what I have and cannot get right.

Any help is much appreciated!

SELECT DISTINCT
--CASE WHEN COUNT(ID) <> 1 THEN Outcome_Met = 'YES' END as CorrectNum,
tp.*
INTO #temp_num
FROM #temp_memID tp --where have pulled needed contact info about member
HAVING count(RID) > 1
GROUP BY RID, Numerator_Met
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Francis OmorutoInfrastructure and Database Design Consultant

Commented:
It really would be best to provide some illustrative data that you can arrange in a worksheet with the expected result.
Cheers!
Aaron TomoskyDirector of Solutions Consulting

Commented:
Start by Doing just a select query that gets the results you want. Don't do the insert yet as your insert is all jumbled in there. We will add that at the end.

So first select what you want to output, group by, and count. Take a crack at that and post that and the data it pulls herr
Top Expert 2012

Commented:
Something like this perhaps:
SELECT  RID,
        Numerator_Met,
        MAX(Outcome_Met) AS CorrectNum
INTO    #temp_num
FROM    #temp_memID tp --where have pulled needed contact info about member
GROUP BY RID,
        Numerator_Met
HAVING  COUNT(RID) > 1

Open in new window

Aaron TomoskyDirector of Solutions Consulting

Commented:
Yep. Sorry I'm on my phone and so not typing out full SQL.

I suggest going with
Insert into whatever
Select whatever from whatever group by something.

By usin this style, you can do the select, then just add the insert above it. Much easier to build an troubleshoot IMO. Select into blah from blah is hard to read, hard to write and test.
Snr. Development Manager
Commented:
Cannot understand fully your requirement but try like this if you want to list only those IDs which exists more than once in the table
SELECT RID, 'YES' as Numerator_Met FROM #temp_memID group by rid HAVING count(*) > 1

Open in new window

Author

Commented:
The query for the below works in giving me the "offending" duplicates where I wanted to get only the "YES" outcome instead of the "NO" --  thank you very much!

And great advice from others on working with just SELECT and not SELECT INTO when facing a challenge.

I would like to be able to now remove these duplicates from my "master" list so that I don't have to go through de-duping them in a spreadsheet later -- I imagine I could do this by a subquery?

SELECT RID,
'YES' as Numerator_Met
FROM #temp_memID
group by rid
HAVING COUNT(RID) > 1

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial