Solved

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

Posted on 2007-11-19
16
172 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
  • 8
  • 7
16 Comments
 
LVL 142

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 142

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
 
LVL 142

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 142

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 142

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 142

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 142

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:ScottPletcher
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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 to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

746 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now