How to create or generate this pattern in sql server( or in asp.net ) for reference ID

I have to create reference Id , which has the following pattern and store it in in referenceID column at tblReferences table

First month

   H01C01001L  
   H01C01002L 
   ...
   H01C01102L

after a month the pattern repeats like this,

  H01C02001L  
  H01C02002L 
   ...
  H01C02102L

third month:

  H01C03001L  
  H01C03002L 
   ...
  H01C03102L

H01 C, and L will not change

upper limit for the sequence in pattern is unknown, but it will stop between 200 and 400 in a month

i am new to sql databases, any suggestion is appreciated.

2 answers

  • answered 2018-11-08 08:44 Andrey Nikolov

    You can use sequences to achieve that. Just create a new one that starts with your first number:

    CREATE SEQUENCE dbo.NumbersSequence AS INT START WITH 1001 INCREMENT BY 1; 
    

    When you need to generate a new reference ID, obtain the next number from the sequence and format it to match your pattern:

    declare @number int
    select @number = NEXT VALUE FOR dbo.NumbersSequence;
    select CONCAT('N01C', FORMAT(@number, 'D5'), 'L')
    

    When you need to change the next value that will be generated (i.e. next month), alter the sequence:

    ALTER SEQUENCE dbo.NumbersSequence RESTART WITH 2001;
    

  • answered 2018-11-08 08:51 George Menoutis

    Here's how I'd approach this. I assume a source table called sourcetable, having a date field named datefield:

    insert into tblReferences (referenceID,...(other cols)...)
        select
            'H01C'
            +convert(char(2),q2.monthindex)
            +convert(char(3),row_number() over (partition by q2.monthindex order by s.datefield asc))
            +'L' as referenceID,
            ...(other cols)...
    from sourcetable s
    cross join (select min(s2.datefield) as firstdate from sourcetable s2)q1  -- gets first date to compare others
    cross apply(select datediff(month,q1.firstdate ,s.datefield as monthindex)q2 -- gets month difference between first and current
    

    You may run the query without the first (insert) line to make sure it produces what you want. As others have commented, this won't work for more than a year, returning values like H01C13001L and so on.