Solved

Autoincrement id field like this YYYY-autonumber

Posted on 2004-09-15
7
275 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 125 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 125 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

739 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