Solved

T-SQL Script to create a reference number from a client name

Posted on 2007-11-19
16
180 Views
Last Modified: 2010-03-20
Hi, I'm tying myself in knots with this one so wondered if anyone could point me in the right direction.

I have a table containing the names of around 3,000 companies (TBL_Companies)

I need to create a reference number for each company based on the name. In simple terms it will be the first four letters of their name followed by a 3 digit number, eg:

Able & Co - ABLE001
South London Cleaners - SOUT001
South Yorkshire Council - SOUT002

So it will give the next number for each company in the group.
 
I haven't a clue how to do this so all suggestions gratefully received! (I have considered resorting to Excel which is how desperate I am!)
0
Comment
Question by:pauldonson
[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
  • 8
  • 7
16 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20312396
please check out this script:
declare @tbl_companies table ( name varchar(50), code varchar(10))
insert into @tbl_companies (name) values  ('Able & Co - ABLE001' )
insert into @tbl_companies (name) values  ('South London Cleaners - SOUT001' )
insert into @tbl_companies (name) values  ('South Yorkshire Council - SOUT002' ) 
select t.name, upper(left(t.name, 4)) + RIGHT( '000' + cast ( ( select count(*) from @tbl_companies i where left(i.name,4) = left(t.name,4) ) as varchar(10)), 3) code
from @tbl_companies  t
order by t.name

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20312402
sorry, 1 condition missing:
declare @tbl_companies table ( name varchar(50), code varchar(10))
insert into @tbl_companies (name) values  ('Able & Co - ABLE001' )
insert into @tbl_companies (name) values  ('South London Cleaners - SOUT001' )
insert into @tbl_companies (name) values  ('South Yorkshire Council - SOUT002' ) 
select t.name, upper(left(t.name, 4)) + RIGHT( '000' + cast ( ( select count(*) from @tbl_companies i where left(i.name,4) = left(t.name,4) and i.name <= t.name ) as varchar(10)), 3) code
from @tbl_companies  t
order by t.name

Open in new window

0
 

Author Comment

by:pauldonson
ID: 20312591
One tiny problem, the table has duplicates in it (which is OK) - so the script above will number them all 004 if there are 4 with the same name instead of 001,002,003,004

Sorry!
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20312640
is there any other field that makes the row "unique"?

if yes, we can adjust the subquery to take that field into account
0
 

Author Comment

by:pauldonson
ID: 20312648
Company_ID is unique
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20312713
ok, let's see what this gives:


select t.name, upper(left(t.name, 4)) + RIGHT( '000' + cast ( ( select count(*) from tbl_companies i where left(i.name,4) = left(t.name,4) and ( i.name < t.name or  ( i.name = t.name and i.company_id <= t.company_id) ) ) as varchar(10)), 3) code
from tbl_companies  t
order by t.name

Open in new window

0
 

Author Comment

by:pauldonson
ID: 20313106
Yep, that's done it! Thanks.

One last thing just so I don't do any damage - how should I format it to do an update, here is what I have:

Update TBL_Companies
Set Sage_account = select t.company_name, upper(left(t.company_name, 4)) + RIGHT( '000' + cast ( ( select count(*) from tbl_companies i where left(i.company_name,4) = left(t.company_name,4) and ( i.company_name < t.company_name or  ( i.company_name = t.company_name and i.company_id <= t.company_id) ) ) as varchar(10)), 3) code
from tbl_companies  t
order by t.company_name


Should this be OK?
0
 

Author Comment

by:pauldonson
ID: 20313117
Hum, apparantly not! I just tested it on the backup database.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20313151
what about this:


Update t
Set Sage_account = select t.company_name, upper(left(t.company_name, 4)) + RIGHT( '000' + cast ( ( select count(*) from tbl_companies i where left(i.company_name,4) = left(t.company_name,4) and ( i.company_name < t.company_name or  ( i.company_name = t.company_name and i.company_id <= t.company_id) ) ) as varchar(10)), 3) code
from tbl_companies  t

Open in new window

0
 

Author Comment

by:pauldonson
ID: 20313249
I get this error:

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'select'.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20313441
the keyword SELECT was too much


Update t
Set Sage_account = t.company_name, upper(left(t.company_name, 4)) + RIGHT( '000' + cast ( ( select count(*) from tbl_companies i where left(i.company_name,4) = left(t.company_name,4) and ( i.company_name < t.company_name or  ( i.company_name = t.company_name and i.company_id <= t.company_id) ) ) as varchar(10)), 3) 
from tbl_companies  t

Open in new window

0
 

Author Comment

by:pauldonson
ID: 20313464
Sorry about this, I get this message now:

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'as'.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20314605
looks like I had a leftover from the query...


Update t
Set Sage_account = upper(left(t.company_name, 4)) 
 + RIGHT( '000' + cast ( ( select count(*) from tbl_companies i where left(i.company_name,4) = left(t.company_name,4) and ( i.company_name < t.company_name or  ( i.company_name = t.company_name and i.company_id <= t.company_id) ) ) as varchar(10)), 3) 
from tbl_companies  t

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 20316297
Since company names can change, you really shouldn't be using the name in the ref num.  Just use a dummy, sequential number you arbitrarily assign; in SQL Server, an IDENTITY column is a typical way to do that.

Seriously, honestly, you *don't* want to include part of the name in your identifier.
0
 

Author Comment

by:pauldonson
ID: 20319189
Hi Scott, thanks for the tip - however this is the Account Code that will be used in their Sage Accounting system - People always like to have these as part of the name (which I know is a pain but that's accountants for you!!!)
0
 

Author Comment

by:pauldonson
ID: 20319964
angell, that did the trick thanks!
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

622 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