Is there any way to generate Alphabetic Sequence with Current Year In SQL

Question

How can we write a SQL function which can generate 'Sequence_Code' like AA,AB,AC....AZ. BA,BB,BC.... with the combination of last two digit of 'Current_Year' for each 'ID'. Order by 'Record_Date'

For instance: If Current_Year of First row is is 2019, then Sequence_Code should be 19AA. My table is LoadData

Sequence_Code	ID	Current_Year	Record_Date
NULL	310001	2019	2019-01-01
NULL	310002	2018	2018-02-22
NULL	310003	2020	2020-02-20
NULL	310004	2020	2020-02-10

Expected Output is:

Sequence_Code	ID	Current_Year	Record_Date
19AA	310001	2019	2019-01-01
18AB	310002	2018	2018-02-22
20AC	310003	2020	2020-02-20
20AD	310004	2020	2020-02-10

Answer 1

With ROW_NUMBER() window function and math:

WITH cte AS (SELECT *, ROW_NUMBER() OVER (ORDER BY Record_Date) rn FROM LoadData)
SELECT RIGHT(Current_Year, 2) + 
       CHAR(ASCII('A') + rn / 26 + CASE rn % 26 WHEN 0 THEN -1 ELSE 0 END) + 
       CHAR(ASCII('A') - 1 + CASE rn % 26 WHEN 0 THEN 26 ELSE rn % 26 END) Sequence_Code,
       ID, Current_Year, Record_Date
FROM cte
ORDER BY rn

If you want to update the column Sequence_Code of the table:

WITH cte AS (SELECT *, ROW_NUMBER() OVER (ORDER BY Record_Date) rn FROM LoadData)
UPDATE cte
SET Sequence_Code = RIGHT(Current_Year, 2) + 
                    CHAR(ASCII('A') + rn / 26 + CASE rn % 26 WHEN 0 THEN -1 ELSE 0 END) + 
                    CHAR(ASCII('A') - 1 + CASE rn % 26 WHEN 0 THEN 26 ELSE rn % 26 END)

See the demo.
Results:

> Sequence_Code |     ID | Current_Year | Record_Date
> :------------ | -----: | -----------: | :----------
> 18AA          | 310001 |         2018 | 2018-01-01 
> 19AB          | 310002 |         2019 | 2019-02-22 
> 20AC          | 310004 |         2020 | 2020-02-10 
> 20AD          | 310003 |         2020 | 2020-02-20