SQLTeam.com | Weblogs | Forums

Auto Number


#1

I want to generate auto number based on region id region is A the i want auto number to start with A-YYMM-001

YY is year and MM is month

if region is B i want to generate auto number to start with B-YYMM-001

if again region B is entering data it should be B-YY-MM-002

if again region A is entering data it should be A-YY-MM-002

Please help


#2

what happens when things reach 999? keep counting to 1000 etc?


#3

yes it has to be like what u mentioned


#4

Store the data properly and this is easy.

Store the data in separate columns, and combine them for output/display only.

Just because you think of it as one piece of data does not mean it has to be stored that way. Just like with a date: the output is mm/dd/yyyy, etc., but it's stored in a completely different format.


#5

You already have the region - so you don't need that part of the auto number. If the value YYMM is derived from another column in the table you don't need that either...

So that leaves you with generating a SEQUENCE number based on the region and the date. At this point it depends on how you determine the sequence. Is that based on a specific ordering of the data - or is it based on when the row is added - and what happens when someone inserts 1000 rows directly into the table?


#6

hi

i know this topic from long ago

i tried something different

if it helps great
:slight_smile:
:slight_smile:

drop create data
use tempdb 
go 

use tempdb 
go 

drop table data
go 

create table data
(
region_id  int , 
region varchar(100) , 
entry_date date 
--, 
--auto_number varchar(100) 
)
go 

insert into data select 1,'A', '2018-01-01'
insert into data select 1,'A', '2018-01-02'
insert into data select 1,'A', '2018-01-03'
insert into data select 1,'A', '2018-01-04'
insert into data select 2,'B', '2018-11-10'
insert into data select 2,'B', '2018-11-11'
insert into data select 2,'B', '2018-11-12'
insert into data select 2,'B', '2018-11-13'
insert into data select 1,'A', '2018-10-11'
insert into data select 1,'A', '2018-11-05'
insert into data select 1,'A', '2018-11-09'
insert into data select 1,'A', '2018-12-04'
go
SQL
;WITH cte 
     AS (SELECT Row_number() 
                  OVER( 
                    ORDER BY (SELECT NULL)) AS rn, 
                region_id, 
                region, 
                entry_date 
         FROM   data), 
     rec_cte 
     AS (SELECT *, 
                1 AS grp 
         FROM   cte 
         WHERE  rn = 1 
         UNION ALL 
         SELECT a.*, 
                CASE 
                  WHEN a.region_id = b.region_id THEN b.grp 
                  ELSE b.grp + 1 
                END 
         FROM   cte a 
                JOIN rec_cte b 
                  ON a.rn = b.rn + 1) 
SELECT region + '-' + CONVERT(CHAR(4), entry_date, 12) 
       + '-' + RIGHT('0000' + Cast( CASE WHEN (grp -1 % 3) = 0 THEN 1 ELSE (grp 
       -1 % 3) 
       END AS VARCHAR), 3) AS ok 
FROM   rec_cte 
ORDER  BY rn 

go
Result