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