Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Autoincrement id field like this YYYY-autonumber

Posted on 2004-09-15
7
Medium Priority
?
278 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
7 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

721 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