# Generation of Auto Series based on logic

• Hello,

I have a requirement where I need to generate values for ID column of table. The series should start like:

10.B001 , 10.B002, 10.B003------10.B999

Once it reaches 999, the series will switch to 10.C001, 10.C002----10.C999

Its an auto generated series with no dependency on any of the columns from table.

Can someone please help with the sql logic? I am bit new to SQL.

thanks,

Kirti

๐

It would be greatly appreciated if you could provide the DDL (create table) script, sample data as an insert statement, the desired output from the sample data, and what you have tried so far.

Your problem is simple, just use a sequence / Tally generator and the mod (%) operator for both the character and the numerical part, remember that 'A' is CHAR(65) ๐

๐

A question, are you basing the series on 999 chunks rather than 1000 chunks, cannot see B000 or C000?

• @ kirti ,

What do you want it to do when you go to 1 more than 10.Z999 ?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Jeff Moden wrote:

What do you want it to do when you go to 1 more than 10.Z999 ?

Only guessing, probably a "Bottles on the wall" sequence with a twist: 09.A001 ๐

๐

• Jeff Moden wrote:

@ kirti ,

What do you want it to do when you go to 1 more than 10.Z999 ?

@ kirti ,

If you could answer the question above, I can make a simple coded solution for you. ๐ย  I also don't need anything else.ย  Your original post explained pretty much everything else just fine.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• there are 6500 rows in total so it will not reach till there

• This reply was modified 1 year, 5 months ago by  kirti.
• `select top(6500)        ROW_NUMBER() OVER (ORDER BY N) ID_INT,       '10.' + char(ascii('A') + N/1000) + right('00' + convert(varchar, N%1000), 3)ID  from fnTally(1,70000) where n%1000 <> 0;`

Create a Tally Function (fnTally)

• Just a tiny simplification of what Jonathan posted and it starts with the "B" series like you wanted...

` SELECT TOP 6500        ID = CONCAT('10.',CHAR(66+t.N/1000),RIGHT(CONCAT('00',t.N%1000),3))   FROM dbo.fnTally(1,70000) t --This returns "N"  WHERE t.N%1000 <> 0  ORDER BY t.N;`

To explain both our code, the N/1000 returns the number of thousands in the number "N", which is used to drive the creation of the letter.ย  CHAR(66) is the letter "B".

The N%1000 returns the right 3 digits and the concatenation of "00" with that provides the correct number of digits including any leading zeros.

Post back if your DBA makes the mistake of not allowing functions, even if they are of the very high performance iTVF (inline Table Valued Function) like that of the fnTally function.

For an explanation of why "B"ย  = CHAR(66), please refer to the following link...

https://www.asciitable.com/

This is even better because it has a lot more tables available...

https://www.lookuptables.com/text/ascii-table

Click on the "Home > " part of the link near the top to see the rest of the tables offered by that site.

The reason for the "overshoot" of "70,000" in the fnTally parameters is simply to make sure than the "000" values that are skipped don't detract from the total of 6500 you wanted.ย  The fnTally function is "smart" enough to know that you only need 6506 values to calculate the 6500 values that you want returned thanks to the TOP 6500 notation that was used in conjunction with the WHERE t.N%1000 <> 0.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Nor trying to be difficult, but that just lists the values, it doesn't assign them as the table ID in the existing table, as stated in the OP.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• In order to do what Scott suggests, you'd need to make a SEQUENCE that counts from 1 to 999 and then recycles back to 1.ย  The formula to convert that would be the same as those posted.ย  Either that, or you can use and IDENTITY column and a persisted computed column with the formula with the understanding that you're going to have gaps because you want each letter sequence to start at "001" instead of "000".ย  Of course, you could add a trigger to fix either of those but now you're getting a bit complicated.ย  There might be a way to fix that using a different "base" but we'll wait to hear back from you.

The problem with any of that is, if you do an unsuccessful insert andย  it rolls back, the number will still be consumed and you'll end up with a gap in your sequence because the SEQUENCE will not be rolled back.ย  Of course, that might be able to be repaired in a trigger, as well.

Since you stated that you only need 6500 values, it might be better to "pre-populate" the table and call it a day but let us know.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• ScottPletcher wrote:

Nor trying to be difficult, but that just lists the values, it doesn't assign them as the table ID in the existing table, as stated in the OP.

Agreed.ย  Do you have a method that you'd like to share?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• I cannot create fnTally function since it's a PLM system and object creation is not allowed.

• kirti wrote:

I cannot create fnTally function since it's a PLM system and object creation is not allowed.

`SELECT TOP(6500)       ROW_NUMBER() OVER (ORDER BY t.N) ID_INT,       CONCAT('10.', CHAR(ASCII('B') + t.N/1000), RIGHT(CONCAT('00', t.N%1000), 3)) ID  FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))           FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) [1](N),               (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) [10](N),               (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) [100](N),               (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) [1000](N)) t(N) WHERE t.N%1000 <> 0;`
• Thanks alot. It helped me resolve the issue.

• Just a small query. Can we start this series from '000' instead of 001?

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply