Need incremented sequence with reset every year

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need incremented sequence with reset every year

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2014-11-06 : 18:49:22

I have a requirement for the increment of a sequence..
sequence numbers are suffixed with year number, every year it should get reset to begin from 1 again.

1.14
2.14
3.14

from 2015 on wards new sequence
1.15
2.15
3.15

Thanks a lot for the helpful info.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-07 : 09:54:24

You'll need to build a stored procedure that does the inserts and build the sequence number there before the insert.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-11-07 : 11:53:54

The actual columns should be two, separate columns: sequence and year. The combined column becomes a computed column; you can persist it if you need to index it:
seq#.yy

That allows an identity for the seq# by just resetting it to 1 at the start of every year.


CREATE TABLE <table_name> (
    seq# int NOT NULL IDENTITY(1, 1),
    year char(2) NOT NULL,
    full_seq AS CAST(seq# AS varchar(10)) + '.' + year
    ,...
    )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources