Auto Increment no

Question

I want to auto-increment a serial number:

"ATN/01-1920" WHERE ATN--given text,01-auto increment,1920--current financial

The result should be like this:

'ATN/01-1920','ATN/02-1920','ATN/03-1920'.....

Also, in a new financial year it should be reset like this: 'ATN/01-2021'

Ajan Balakumaran · Answer 1 · 2019-04-05T11:39:42.363

0

Give your start date and end date then the script will generate the serial accordingly pass the date in the format YYYYMMDD

SELECT  concat('ATN/',format(DATEADD(month, nbr - 1, '19200101'),'MM'),'-', year(DATEADD(month, nbr - 1, '19200101')))
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr
          FROM      sys.columns c
        ) nbrs
WHERE   nbr - 1 <= DATEDIFF(MONTH, '19200101', SYSDATETIME())

edited Apr 05 '19 at 11:39

answered Apr 04 '19 at 12:05

Ajan Balakumaran

1,639
1
8
16

Does this prevent collisions? – onedaywhen Apr 04 '19 at 12:19
why start date and end date?is there any requirement? is it not possible to get a financial year from the current date? i want financial wise auto serial no like 'ATN/01-1920','ATN/02-1920','ATN/03-1920' – Solomon Behera Apr 05 '19 at 11:36
so you want from 1920 to current data, try the updated answer? – Ajan Balakumaran Apr 05 '19 at 11:36

Auto Increment no

1 Answers1