Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 189
  • Last Modified:

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

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
pauldonson
Asked:
pauldonson
  • 8
  • 7
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
pauldonsonAuthor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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

if yes, we can adjust the subquery to take that field into account
0
 
pauldonsonAuthor Commented:
Company_ID is unique
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
pauldonsonAuthor Commented:
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
 
pauldonsonAuthor Commented:
Hum, apparantly not! I just tested it on the backup database.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
pauldonsonAuthor Commented:
I get this error:

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'select'.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
pauldonsonAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
pauldonsonAuthor Commented:
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
 
pauldonsonAuthor Commented:
angell, that did the trick thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now