Solved

Autoincrement id field like this YYYY-autonumber

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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

828 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