asked on

TSQL case statement when a value is in the column more than 1 time

I am struggling with a TSQL query that will do a CASE like function but only when the value already exists in the column. (sudo code below)

The requirement is that if the phone number already exists in the system TWICE, then we return YES, if not, we return NO. I can't figure out how to CASE on COUNT(phone_number) > 1.

I know I can create an inline view and left join to it, but I was hoping there is a more elegant solution.

SELECT 
 [first_name],
 [last_name],
 CASE [phone_number]
    IF count [phone_number] >= 2 THEN 'Yes'
    ELSE 'No'
 END as [known_to_system]

FROM person_table

Open in new window

Thanks experts!!!

Vitor Montalvão

How a phone_number can exist twice? Can you give us an example of this behavior?

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sqlagent007

ASKER

Thank you @Jim Horn!! Let me provide some dummy data and what I have been doing. (See attached ceate_dumm_table.sql)

Here is the query that gives me the results, I was just hoping for a more elegant solution.

SELECT DISTINCT p.first_name, 
                p.last_name, 
                CASE 
                  WHEN g.phn_count = 1 THEN 'No' 
                  WHEN g.phn_count >= 2 THEN 'Yes' 
                END AS [known_to_system] 
FROM   person_table p 
       LEFT JOIN (SELECT first_name, 
                         last_name, 
                         Count(phone_number) AS phn_count 
                  FROM   [dbo].[person_table] 
                  GROUP  BY first_name, 
                            last_name) g 
              ON p.first_name = g.first_name 
                 AND p.last_name = g.last_name

Open in new window

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sqlagent007

ASKER

Ok, I am gathering the only way to do this is with a JOIN. Thanks experts!

SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sqlagent007

ASKER

Thanks experts! The LEFT JOIN ended up giving me the most control over what I was doing. I ended up needing additional conditions and was able to to that with the INLINE VIEW.

Thanks again for all your help!