SQL help needed

Hi Experts

I have a rather complicated stored proc that does take a few seconds to run, it loads customer data from several sources into a temp table, and returns that table.

What I need now is a way to mark customers that have the same name but different ID's so the users can perform additional checks to make sure they have the right one.

I can't think of an efficient way to do this. Maybe a count on name in the temp table?

Andy
LVL 3
Andy GreenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Use the HAVING clause.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Here's my code:

drop table Customers
go

create table Customers
(ID int,
Name varchar(50),
Warning bit null)
go

insert into Customers(ID, Name)
values (1, 'FirstName'), (2, 'SecondName'), (3, 'ThirdName'), (4, 'FirstName')
go

update Customers
set Warning = 0
from Customers
go

with RowsToUpdate as
(select Name, Warning
From Customers
group by Name, Warning
having Count(ID)>1)

update Customers
set Warning = 1
from Customers 
inner join RowsToUpdate 
on Customers.Name = RowsToUpdate.Name

select * from Customers

Open in new window

0
PortletPaulfreelancerCommented:
Not sure what the field is that you are evaluating, but assuming it's [CUSTOMER_ID] then you would want DISTINCT in the evaluation I think. e.g.

HAVING COUNT(DISTINCT [CUSTOMER_ID]) > 1

e.g.
select *
from staging 
where customer in (
          select Customer
          from staging
          group by Customer
          HAVING COUNT(DISTINCT [CUSTOMER_ID]) > 1
          )

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Andy GreenAuthor Commented:
Its just customer name, its to identify the same name but different ID.

Trying your solution Phillip. Thanks

Andy
0
Andy GreenAuthor Commented:
Phillip I'm using a table variable for my temp table, and The sample you supplied works fine as you have it but not with a table variable,

Declare @Customers table
(ID int,
Name varchar(50),
Warning bit null)

Andy
0
Andy GreenAuthor Commented:
ALso Its not a limited list I need rather as Phillip has done it all the rows with a flag if the name exists in another record.

Andy
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
It does work - you need to get rid of the GO statements and alias the tables.

declare @Customers as table
(ID int,
Name varchar(50),
Warning bit null)

insert into @Customers(ID, Name)
values (1, 'FirstName'), (2, 'SecondName'), (3, 'ThirdName'), (4, 'FirstName')

update @Customers
set Warning = 0
from Customers;


with RowsToUpdate as
(select Name, Warning
From @Customers
group by Name, Warning
having Count(ID)>1)

update @Customers
set Warning = 1
from @Customers C
inner join RowsToUpdate R
on C.Name = R.Name

select * from @Customers

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Andy GreenAuthor Commented:
I did but it looks like you have to alias the table as you have done.

Andy
0
Andy GreenAuthor Commented:
Nearly there, just struggling on one last point,  It is possible that the same Customer could appear in the list 2 or 3 times, I don't want these highlighted, just those with different ID'd  but the same names.

This is in Wales where same names can be common.

Andy
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Change the line

having Count(ID)>1)

to

having Count(DISTINCT ID)>1)
0
Andy GreenAuthor Commented:
Sorry still not right:

In this an expanded version of your original code I don't want 4 FirstName to have a warning:

declare @Customers as table
(ID int,
Name varchar(50),
Warning bit null)

insert into @Customers(ID, Name)
values (1, 'FirstName'), (2, 'SecondName'), (3, 'ThirdName'), (4, 'FirstName'), (1, 'FirstName')

update @Customers
set Warning = 0
from @Customers;


with RowsToUpdate as
(select Name, Warning
From @Customers
group by Name, Warning
having Count(Distinct ID)>1)

update @Customers
set Warning = 1
from @Customers C
inner join RowsToUpdate R
on C.Name = R.Name

select * from @Customers
0
Andy GreenAuthor Commented:
Hang on ignore that - I'm getting confused now.

Andy
0
Andy GreenAuthor Commented:
Sorry Phillip, you are right.  Worked fine with Distinct.

Andy
0
Andy GreenAuthor Commented:
Excellent - Thank you.

Andy
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You may perhaps want to change the very final line to

select distinct * from @Customers
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.