SQL - Autogenarating Customer IDs

I have been swamped lately working on 3 different projects, and it's been difficult to get back to blogging as often as I am used to. However, this week I found myself with an interesting situation where my client needed to import a customer master list into GP, but was migrating from a system that autoassigned an internal key to each customer record. My client wanted to move away from this "unique internal number" to an alphanumeric nomenclation similar to that of Fabrikam, for example, Aaron Fitz would have a customer ID of AARONFIT0001. They thought it was a very intuitive way of identifying customers and that it would serve the AR staff well.

The customer data was staged in a SQL database and the goal was to create a query that will assign these customer IDs based on the customer name. So, here is the solution:

AutoCustomerID.sql

-- Created by Mariano Gomez, MVP
SELECT
 -- evaluates the 9 first characters of the customer name and removes any blanks
 -- other characters can be removed) in between those first 9 characters for a
 -- total of 8, adds an extra zero if needed to complete 9 characters
 CASE LEN(UPPER(REPLACE(SUBSTRING(CUSTNAME, 1, 9), ' ', ''))) 
   WHEN 8 THEN UPPER(REPLACE(SUBSTRING(CUSTNAME, 1, 9), ' ', '')) + '0'
   ELSE UPPER(REPLACE(SUBSTRING(CUSTNAME, 1, 9), ' ', '')) END +

 -- accounts for the rest of the string, uses the rank function to do the numbering,
 -- partitioning by customer name. Just in case there is more than one customer
 -- with the same starting 9 characters, rank() will number them sequentially
 SUBSTRING('000', 1, 3 - LEN(CONVERT(CHAR(3), RANK() OVER(PARTITION BY REPLACE
   (SUBSTRING(CUSTNAME, 1, 9), ' ', '') ORDER BY CUSTNAME))))
   + CONVERT(CHAR(3), RANK() OVER(PARTITION BY REPLACE(SUBSTRING(CUSTNAME, 1,
   9), ' ', '') ORDER BY CUSTNAME))
FROM RM00101
ORDER BY CUSTNAME

Run this against the TWO database to see the results.

If you ever have the need to create customer IDs based on the customer name this should give you a starting point.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/