Results to merge customer names

Question

Post reply

Results to merge customer names

iwatson

SSC Enthusiast

Points: 190
More actions
November 30, 2010 at 6:42 am

#243401

Hi
I have a SSRS report which is working but I want to merge some customers together e.g.
Select Customername, Value1
From Customer
example output:
MyCompany Ltd, 500
MyCompany Group Ltd, 250
I get this output because we have setup a Group customer who is the "main" office and any branches aren't the Group headQuarters.
I would like the output to be:
MyComapany Ltd, 750
But on my report i want these 2 to merge so I can see "Value 1" as a whole and not seperated between the 2 accounts. Is there anyway to merge customername if contains Group...
Please help if you can?
Regards
Ian

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11412 More actions · Answer 1

How do you determine that these 2 are the same company?

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323518 More actions · Answer 2

i think you'll have to have some ugly cleanups to try to find possible matches:

SELECT * FROM

(

Select PK,

REPLACE(

REPLACE(Customername

,'Group','')

,'ltd','')

,'co','')

,'Company','')

,'Inc','')

,'Incorporated','') AS Customername

)

From Customer

Group By Customer

HAVING COUNT(Customer) < 1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

Be careful with the above code, you want to make sure you using spacing well, and you'll want to invert the subset choices.

For example:

DECLARE @CustomerName VARCHAR(100)

SET @CustomerName = 'Bob Jocos Company'

Select

REPLACE(

REPLACE(@Customername

,'Group','')

,'ltd','')

,'co','')

,'Company','')

,'Inc','')

,'Incorporated','') AS Customername

Produces this:

Bob Jos mpany

DECLARE @CustomerName VARCHAR(100)

SET @CustomerName = 'Bob Jocos Company'

Select

REPLACE(

REPLACE(@Customername

,'Group','')

,'ltd','')

,'Company','')

,'co','')

,'Incorporated','')

,'Inc','') AS Customername

Produces: (Note the inversion for co/company now properly removing company)

Bob Jos

And this:

DECLARE @CustomerName VARCHAR(100)

SET @CustomerName = 'Bob Jocos Company'

SET @CustomerName = ' ' + @CustomerName + ' '

Select

LTRIM(RTRIM(

REPLACE(

REPLACE(@Customername

,' Group ','')

,' ltd ','')

,' Company ','')

,' co ','')

,' Incorporated ','')

,' Inc ','')

)) AS Customername

Is where you really want to get to with that:

Bob Jocos

This isn't pretty. Check into fuzzy lookup, it may get you where you need to be. Otherwise Lowell has the right idea, I just wanted to show you a few of the 'gotchas' trying to do a cleanup like that if you need to use it.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA