Generation of Auto Series based on logic

Question

Post reply

Generation of Auto Series based on logic

kirti

Old Hand

Points: 387
More actions
January 6, 2023 at 2:38 pm

#4134541

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

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

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

We will need your help in order to be able to help you, so please help us!

😎

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?

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 2

@ 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.

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

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 SSC Guru Points: 1003865 More actions · Answer 4

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.

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

kirti Old Hand Points: 387 More actions · Answer 5

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

This reply was modified 1 year, 3 months ago by kirti.

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 6

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)

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 7

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.

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

ScottPletcher SSC Guru Points: 100949 More actions · Answer 8

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.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 9

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.

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

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 10

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.

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

kirti Old Hand Points: 387 More actions · Answer 11

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

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 12

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;

kirti Old Hand Points: 387 More actions · Answer 13

kirti

Old Hand

Points: 387

January 9, 2023 at 4:14 am

#4135256

Thanks alot. It helped me resolve the issue.

kirti Old Hand Points: 387 More actions · Answer 14

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