Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How can we Write a SQL function to Increment Alphabetic 'Sequence_Code' for only those ID whose Status is'A'
Alphabetic 'Sequence_Code' is combination of Last Two Digit of 'Current_Year' and 2 character alphabet like AA,AB,AC....AZ, BA,BB,BC...BZ. CA,CB,CC....

My Table 'LoadData' is as below:

Sequence_Code |     ID | Current_Year | Record_Date| Status     |
 :------------ | -----: | -----------: | :----------|          --|
 17AA          | 310001 |         2017 | 2017-01-01 | S
 18AB          | 310002 |         2018 | 2018-02-22 | S
 19AC          | 310003 |         2019 | 2019-02-10 | S
 20AD          | 310004 |         2019 | 2019-02-20 | A
 20AE          | 310005 |         2020 | 2020-03-20 | S
 NULL          | 310006 |         2020 | 2020-04-20 | A


IF Last Sequence_Code is haveing AZ(In last 2 char), then from next ID it should generate like BA, BB..and so on..

Expected Output is:
Sequence_Code |     ID | Current_Year | Record_Date| Status     |
 :------------ | -----: | -----------: | :----------|          --|
 17AA          | 310001 |         2017 | 2017-01-01 | S
 18AB          | 310002 |         2018 | 2018-02-22 | S
 19AC          | 310003 |         2019 | 2019-02-10 | S
 20AD          | 310004 |         2019 | 2019-02-20 | A
 20AE          | 310005 |         2020 | 2020-03-20 | S
 20AF          | 310006 |         2020 | 2020-04-20 | A



Please suggest if there is any convenient way..

What I have tried:

I tried the below code,But i could not write a proper logic, so it's not working.

SQL
 UPDATE LoadData
SET Sequence_Code = SUBSTRING(MAX(Sequence_Code ),1,3)+ CHAR(ASCII(SUBSTRING(MAX(Sequence_Code ),3,4))+1)
Where Status='I' AND Sequence_Code is NULL
Posted
Updated 4-Jan-21 1:58am
v2

1 solution

Please, take a look here: Custom Auto-Generated Sequences with SQL Server - SQLTeam.com[^]

There you'll find few very interesting functions with detailed information about algorithm.

All what you need to do is to get one of existing functions and to change to your needs.
Here is an idea:
SQL
;WITH CTE AS
(
    SELECT 0 CurrNo
    UNION ALL
    SELECT CurrNo+1
    FROM CTE
    WHERE CurrNo+1<100
)
SELECT CurrNo, char(CurrNo /26 % 26 + 65) + char(CurrNo  % 26 + 65) CurrCode
FROM CTE


Result:

CurrNo 	CurrCode
0 	AA
1 	AB
2 	AC
3 	AD
4 	AE
5 	AF
6 	AG
7 	AH
8 	AI
9 	AJ
10 	AK
11 	AL
12 	AM
13 	AN
...
96 	DS
97 	DT
98 	DU
99 	DV

SQL Server 2019 | db<>fiddle[^]

So, to get digit representation od code, you need to convert it:
SQL
;WITH InitData AS
(
  SELECT '20AZ' Sequence_Code
)
SELECT a.*, char((SumOfDigits +1) /26 % 26 + 65) + char((SumOfDigits +1) % 26 + 65) NewCode
FROM 
(
  SELECT Sequence_Code, LEFT(Sequence_Code, 2) CurrentYear, 
    RIGHT(Sequence_Code, 2) Curr_Code, 
    ASCII(SUBSTRING(Sequence_Code, 3, 1))-65 FirstDigitOfCode, 
    ASCII(RIGHT(Sequence_Code, 1))-65 SecondDigitOfCode,
    ASCII(SUBSTRING(Sequence_Code, 3, 1))-65  +
    ASCII(RIGHT(Sequence_Code, 1))-65 SumOfDigits
  FROM InitData
) a


Result:
Sequence_Code 	CurrentYear 	Curr_Code 	FirstDigitOfCode 	SecondDigitOfCode 	SumOfDigits 	NewCode
20AZ 			20		 	AZ 			0		 	25 			25	 	BA


db<>fiddle#2[^]

Good luck!
 
Share this answer
 
v5
Comments
Member 15036870 4-Jan-21 8:43am    
@MaciejLos I appriciate the response.. This code will generate Sequence_Code,for entire table(From first Row to Last Row). But My Requirement is a bit different, I already have this Sequence_Code in the table,Now If a new row comes with Status 'A',we need to Generate Next Sequence_Code for that row.
Maciej Los 4-Jan-21 8:45am    
Take a look at updated answer ;)
Jörgen Andersson 5-Jan-21 6:30am    
👍
Maciej Los 5-Jan-21 8:17am    
Thank you, Jörgen.
Happy New Year!
Member 15036870 5-Jan-21 10:01am    
Thank you so much.. It's working. :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900