Solved

Autoincrement id field like this YYYY-autonumber

Posted on 2004-09-15
7
270 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
7 Comments
 
LVL 15

Expert Comment

by:jdlambert1
Comment Utility
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
Comment Utility
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 125 total points
Comment Utility
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 125 total points
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now