CODEWITHSUNDEEP
sqlsql-serverazure-sql-database
Philip
Philip

Reputation: 2628

Generating a Sequential Client Number

I have a client based system that is needing a sequential client number in the form of the following.

First client would get A001, and then each new client through to A999. Once it hits A999, it would continue to B001-B999, and so on until Z001-Z999, when it would reset to AA001-AA999 and so on through the alphabet.

Does anyone see a way of how this could be achieved?

Upvotes: 3

Views: 532

Answers (3)

Richard Hansell
Richard Hansell

Reputation: 5403

This will give you the exact numbers you asked for from A001 to ZZ999. If you want more numbers than that you will need to add logic for a third letter, etc. Note that you aren't getting 1000 numbers per letter, which makes things slightly more awkward.

WITH Numbers AS (
    SELECT 1 AS number
    UNION ALL
    SELECT number + 1 AS number FROM Numbers WHERE number < 701298)
SELECT 
    number,
    CASE WHEN number > 25974 THEN CHAR(64 + (number - 1) / 25974) ELSE '' END --This is the first letter (optional)
        + CHAR(65 + ((number - 1) / 999) % 26) --This is the second letter
        + FORMAT(CASE WHEN number < 1000 THEN number ELSE CASE WHEN number % 999 = 0 THEN 999 ELSE number % 999 END END, 'd3') --This is the three digit number
    AS client_id
FROM 
    Numbers 
OPTION (MAXRECURSION 0);

The Numbers CTE is just to get a suitable number of numbers (1 - 701,298). Once I have them I need to find the boundaries when the second letter changes (every 999 numbers) or the first letter changes (every 26 * 999 = 25974 numbers). Note that the first letter is suppressed until needed.

This gives you 27 * 26 * 999 client ids (the first letter can be blank or A-Z = so 27 options, the second letter can be A-Z = 26 options, the number can be 001-999 = 999 options). That's a grand total of 701,298 client ids.

I would suggest either using an IDENTITY column, or a SEQUENCE to get the "internal" id (which would be a primary key candidate), and then use a function to calculate the client id from this number. That's safer for multiple users, etc. You could use a calculated column, but that's a pretty big overhead?

Upvotes: 2

LONG
LONG

Reputation: 4620

EXAMPLE from the comments: (may not be an answer, posted here just because it is long)

    CREATE SEQUENCE Numbers 
    INCREMENT BY 1 
    MINVALUE 1
    MAXVALUE 999
    CYCLE
    ;


--DROP TABLE test_DL
      Create table test_DL
    (
     VendorName varchar(50),
     VendorId as LeadingCharacters + CAST(FORMAT(TailingNumbers,'000') as VARCHAR(10)),
     LeadingCharacters VARCHAR(50),
     TailingNumbers INT DEFAULT(NEXT VALUE FOR Numbers),
     [Counter] INT IDENTITY(1,1)
     )

--ALTER SEQUENCE Numbers RESTART WITH 1

DECLARE @CONTROL INT = 0

WHILE (@CONTROL < 250)
BEGIN

INSERT INTO test_DL (VendorName)
VALUES ('THIS'),('IS'),('AN'),('EXAMPLE')

SET @CONTROL = @CONTROL + 1

END

;
WITH CTE
AS
(
SELECT *, ROW_NUMBER()OVER(ORDER BY [Counter],TailingNumbers) as RowNumber
FROM test_DL
)

UPDATE CTE
SET LeadingCharacters = CASE WHEN RowNumber <= 999 THEN 'A' WHEN 999 < RowNumber AND RowNumber < 2* 999 THEN 'B' END --The MOST ANNOYING PART is here, you need to manually category all the possibles


SELECT * FROM test_DL  --Run this to check the result

Above method will be very dumb for future updates. Just give you some ideas lol

Upvotes: 0

Yossi Vainshtein
Yossi Vainshtein

Reputation: 4005

I'd use simple integers as the key and a stored procedure (or calculated column) which translates to your desired format. I't essentially a numeric operation, check this SQL which calculates the format.

It does assume that you have no more than 2 letters in the beginning, so number of clients is under 26 * 26 * 1000.

select tmp.num as client_num, CONCAT(
CASE WHEN tmp.num < 26000 THEN '' ELSE CHAR(ASCII('A') - 1 + (tmp.num / 26000)) END,
CHAR (ASCII('A') + (tmp.num / 1000) % 26),
RIGHT('000'+CAST(tmp.num % 1000 AS VARCHAR(3)),3)) as client_id
from

(select 1 as 'num'
union
select 10
union 
select 150
union 
select 1000
union
select 25999
union 
select 26000
union 
select 27000
union 
select 100000) tmp

Returns table:

+------------+-----------+
| client_num | client_id |
+------------+-----------+
|          1 | A001      |
|         10 | A010      |
|        150 | A150      |
|       1000 | B000      |
|      25999 | Z999      |
|      26000 | AA000     |
|      27000 | AB000     |
|     100000 | CW000     |
+------------+-----------+

Upvotes: 2

Related Questions