Improve company productivity with a Business Account.Sign Up

x
?
Solved

Autoincrement id field like this YYYY-autonumber

Posted on 2004-09-15
7
Medium Priority
?
283 Views
Last Modified: 2008-03-04
I would like to create autoincrementing id fields that increment up one on the last digits, but also autoincrements the year on January 1. So the numbers would be like this beginning in 2004...
2004-0001
2004-0002
2004-0003

Then on January 1 2005, it changes to this...

2005-0001
2005-0002
2005-0003
etc.

Is there an easy way to do this in one field or should I try to do it in two fields, and concatenate. I REALLY would like to do this in one field...
Thanks for your help, any ideas would be appreciated...
Bauerchick
0
Comment
Question by:Bauerchick
  • 2
4 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12071188
You can do this in one column, but you should not. In fancy terms, it's a violation of the First Normal form. In practical terms, it'll cause problems and you'll regret it. Problems can range from annoying to very harmful. The most harmful usually arises from someone getting confused, constructing a query incorrectly, and then basing a decision on misleading results.

The easiest way to do this correctly is to make a small integer column for the year, and give it a default of Year(GetDate()), and an integer column defined as an identity.
0
 

Author Comment

by:Bauerchick
ID: 12071335
Ok... so I will use a standard id field which increments. But could you tell me how to implement a field which does the incrementing the way I want, so I can use it to assign project numbers for me? I am using Enterprise Manager to design the table structure. I am fairly new at this stuff, so I really appreciate your patience and help.

Bauerchick :)
0
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 500 total points
ID: 12071473
Sure. In EM, after choosing New Table or opening an existing table in design view, put in a name for a column, set the datatype to small integer, and in the "Default Value" box at the bottom, enter "Year(GetDate())" -- without the quote marks. The GetDate() function returns the system date/time, and the Year function extracts just the 4 digit year.

You can't use the handy "Identity" defintion for a column, because that doesn't allow numbers to be reused, so you have to do it with an "Insert Trigger", like this:

If your table is named "TableA", it's primary key is "ID", the year column is "Yr", and the incrementing number column is "Num"

CREATE TRIGGER trig_inc ON TableA FOR INSERT AS
BEGIN

  DECLARE @num int
  SELECT @num = Max(Num) FROM TableA WHERE Yr = Year(GetDate())

  IF @num IS NULL
    SET @num = 0

  UPDATE a SET Num = @num + 1
  FROM TableA a INNER JOIN inserted i ON a.ID = i.ID

END

This code gets executed every time a new record is inserted. A variable gets populated with the highest value for Num for the current year, and if there isn't one for the current year, the varible is set to zero. The row that was just inserted (without a value for Num) is set to the variable plus 1. "Inserted" is a special table accessible only by triggers, with the same format as the table being inserted into (TableA in this case), and it's used here to limit the update to only the record that was just updated.
0
 
LVL 6

Assisted Solution

by:OlegP
OlegP earned 500 total points
ID: 12072626
If you want use the trigger you don't need to add a column.
for example you table has the following structure TBL(ID_YEAR varchar(10),N1 VARCHAR(10),N2 VARCHAR(10))

CREATE TRIGGER INC_TO_YEAR ON TBL FOR INSERT AS
BEGIN

  DECLARE @MAX_REC VARCHAR(10)
  DECLARE @MAX_YEAR VARCHAR(10)
  DECLARE @MAX_ID VARCHAR(10)

  SELECT @MAX_REC =ID_YEAR
  FROM   INSERTED
  IF @MAX_REC IS NULL
     BEGIN
       SELECT @MAX_REC = Max(ID_YEAR) FROM TBL
       IF @MAX_REC IS NULL SET @MAX_REC = LTRIM(STR(YEAR(getdate())))+'-0000'
       SET @MAX_YEAR=SUBSTRING(@MAX_REC,1,4)
       SET @MAX_ID=SUBSTRING(@MAX_REC,6,4)
       IF CAST(@MAX_YEAR AS INT)<>YEAR(getdate())
           BEGIN
             SET @MAX_REC = LTRIM(STR(YEAR(getdate())))+'-0001'
           END
        ELSE
           BEGIN
             SET @MAX_ID=LTRIM(STR(CAST(@MAX_ID AS INT)+1))
             SET @MAX_ID=REPLICATE('0',4-LEN(@MAX_ID))+@MAX_ID
             SET @MAX_REC = @MAX_YEAR+'-'+@MAX_ID
           END
       INSERT INTO TBL(ID_YEAR,N1,N2)  
       SELECT @MAX_REC,N1,N2 FROM INSERTED
       DELETE  FROM TBL WHERE ID_YEAR IS NULL
     END
END



works perfectly
0

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Here is how to restore SQL Server database to the point in time.  Follow the step by step approach to restore your database at a specific point in time and also understand its alternate approach.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question