SQL restart number sequence each year

Question

I'm Working to create a simple repair log serial number. I would like to create a unique code for each repair but I don't want the numbers continuing on forever, I would like to use Zone(department), Year and Number to identify each repair. I'm Using SQL server 17. I know I can concatenate the fields together to create the number but I'm not sure how to reset the number at the start of the year?

(picture to help describe)

Thanks!

What is wrong with a simple identity? Any kind of roll your own renumbering like this is challenging and has concurrency problems. — Sean Lange, Feb 06 '19 at 16:19
IMO, RepairNumber should be padded with zeros out to one digit more than you expect you would ever need in a year. A190001 for example. This allows for natural sorting. I would use a default value on the RepairNumber column based on a function that is called on insert. Add a unique constraint and you should be set. — UnhandledExcepSean, Feb 06 '19 at 16:24
Example of a column default set by function: https://stackoverflow.com/questions/442503/bind-a-column-default-value-to-a-function-in-sql-2005 — UnhandledExcepSean, Feb 06 '19 at 16:30

score 0 · Answer 1 · answered Feb 06 '19 at 16:19

Assuming you don't want to allow ties, use Row_Number like so:

Select *, Row_Number() over (partition by Year order by [date?])
From MyTable

Replace [date?] with whatever identifier(s) you have to signal the order within the year.

partition by year tells it to restart the numbering at 1 with each new year.

score 0 · Answer 2 · answered Feb 06 '19 at 16:19

This generates randomly sorted "repair numbers"

SELECT 
  zone, 
  year, 
  row_number() OVER (PARTITION BY year ORDER BY (SELECT 1)) AS [Repair Number]
FROM t

Concatenated:

SELECT
  zone,
  year,
  [Repair Number],
  zone + CAST(year AS VARCHAR) + CAST([Repair Number] AS VARCHAR) AS [Concatenated]
FROM (
  SELECT 
    zone, 
    year, 
    row_number() OVER (PARTITION BY year ORDER BY (SELECT 1)) AS [Repair Number]
  FROM t
) t

Of course, you should probably not assign random numbers, but assign them deterministically, e.g. based on an ID / identity, etc, so replace ORDER BY (SELECT 1) by something more meaningful.

score 0 · Accepted Answer · answered Feb 06 '19 at 16:21

If you want a unique number for each row in the table, then use an identity primary key. It will not reset at each year. It might have gaps -- say when a row is deleted. But it is the most efficient way to accomplish what you want.

If you try to create your own value on input, then essentially you have to lock the entire table while you calculate the next number. This locks out other updates and inserts into the table, so it is an expensive operation. If you do not do this, you will be subject to race conditions, where two different threads might be assigned the same number.

An identity column may not be aesthetically pleasing, but it is really the best solution for identifying rows in a table.

benjamin moskovits · Answer 4 · 2019-02-06T16:39:43.840

0

If you are using an identity value you can reset the seed identity to anything you want with:

DBCC CHECKIDENT ('TableName', RESEED, 5000)

Just remember that identity does not guarantee unique values - just one greater the previous value used. The above code resets the next identity to 5001.

If you are using a sequence you can change the sequence with an

ALTER SEQUENCE dbo.Seq1 RESTART WITH 5000;

edited Feb 06 '19 at 16:39

answered Feb 06 '19 at 16:32

benjamin moskovits

5,261
1
12
22

The problem here is this is not a really good solution because it is not dynamic. What happens when you need to add a row to the previous year? You have to fiddle with your identity or sequence and hope nobody else inserts while you are doing that. Additionally you have to go fiddle with it every year when you need to use a new value. – Sean Lange Feb 06 '19 at 16:44
Its true if you are adding in a previous year. This has happened to me exactly zero times in my lengthy career. Typically you are adding rows for the year you are currently in. Why eventually the sun will flame out and then years won't count as we will have to move to another planet. – benjamin moskovits Feb 06 '19 at 17:09
Right but even if you don't have to add data to old years you still have to go manually fiddle with this every year. – Sean Lange Feb 06 '19 at 17:12

SQL restart number sequence each year

4 Answers4