Solved

SQL help needed

Posted on 2014-10-10
15
142 Views
Last Modified: 2014-10-10
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
0
Comment
Question by:Andy Green
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
15 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40372623
Use the HAVING clause.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40372635
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40372644
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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
LVL 3

Author Comment

by:Andy Green
ID: 40372748
Its just customer name, its to identify the same name but different ID.

Trying your solution Phillip. Thanks

Andy
0
 
LVL 3

Author Comment

by:Andy Green
ID: 40372886
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
 
LVL 3

Author Comment

by:Andy Green
ID: 40372898
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
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40372901
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
 
LVL 3

Author Comment

by:Andy Green
ID: 40372927
I did but it looks like you have to alias the table as you have done.

Andy
0
 
LVL 3

Author Comment

by:Andy Green
ID: 40373212
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40373218
Change the line

having Count(ID)>1)

to

having Count(DISTINCT ID)>1)
0
 
LVL 3

Author Comment

by:Andy Green
ID: 40373246
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
 
LVL 3

Author Comment

by:Andy Green
ID: 40373249
Hang on ignore that - I'm getting confused now.

Andy
0
 
LVL 3

Author Comment

by:Andy Green
ID: 40373254
Sorry Phillip, you are right.  Worked fine with Distinct.

Andy
0
 
LVL 3

Author Closing Comment

by:Andy Green
ID: 40373258
Excellent - Thank you.

Andy
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40373259
You may perhaps want to change the very final line to

select distinct * from @Customers
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question