Andy Green
asked on
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
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
Use the HAVING clause.
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
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.
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
)
ASKER
Its just customer name, its to identify the same name but different ID.
Trying your solution Phillip. Thanks
Andy
Trying your solution Phillip. Thanks
Andy
ASKER
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
Declare @Customers table
(ID int,
Name varchar(50),
Warning bit null)
Andy
ASKER
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
Andy
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I did but it looks like you have to alias the table as you have done.
Andy
Andy
ASKER
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
This is in Wales where same names can be common.
Andy
Change the line
having Count(ID)>1)
to
having Count(DISTINCT ID)>1)
having Count(ID)>1)
to
having Count(DISTINCT ID)>1)
ASKER
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
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
ASKER
Hang on ignore that - I'm getting confused now.
Andy
Andy
ASKER
Sorry Phillip, you are right. Worked fine with Distinct.
Andy
Andy
ASKER
Excellent - Thank you.
Andy
Andy
You may perhaps want to change the very final line to
select distinct * from @Customers
select distinct * from @Customers