Generate an ID Number that resets every 1.Januar

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Generate an ID Number that resets every 1.Januar

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2009-10-20 : 06:55:20

I have a running ID in a Database and would like to create a column that generates an Anual_ID that gets reset to 0 on each 1st of January.

I tried to set this up by the use of the function, where my idea is to apply the following algorithm:

For any record establish the Anual_ID, by getting the MAX(ID) of all records that have been created a year earlier and substracting that number from the running ID.

So I did:

CREATE FUNCTION dbo.SetNewID (
@Input INT
)
RETURNS INT
AS
BEGIN
RETURN (SELECT TOP 1 dbo.RMA_Table.ID - MAX(RMA_Table_1.ID)
FROM dbo.RMA_Table CROSS JOIN dbo.RMA_Table AS RMA_Table_1
WHERE (YEAR(RMA_Table_1.CREATEDDATE) = YEAR(GETDATE()) - 1)
GROUP BY dbo.RMA_Table.ID, YEAR(dbo.RMA_Table.CREATEDDATE))
END

Being Createddate a timeprint of the moment that record is created.

Any suggestions.
Cheers Martin

barnabeck
Posting Yak Master

236 Posts

Posted - 2009-10-20 : 07:13:01

... I forgot to say - which is pretty evident - that it doesn't work. The closest I got was:

SELECT dbo.RMA_Table.ID - MAX(RMA_Table_1.ID) AS Expr1
FROM dbo.RMA_Table CROSS JOIN dbo.RMA_Table AS RMA_Table_1
WHERE (YEAR(RMA_Table_1.CREATEDDATE) = YEAR(GETDATE()) - 1)
GROUP BY dbo.RMA_Table.ID, YEAR(dbo.RMA_Table.CREATEDDATE)

this gives the right ID in case I create a new record!!! Perfect would be to set this query as the preestablished value for the Anual_ID... but SQL doesn't allow formulas there. The idea using a function fails, because this would cause the field to be calculated for all records of tha DB, while I need the calculation just to be done on the new creation of a record... now it should be somehow clearer.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-10-20 : 09:26:54

Simple route...Why not just take your database offline (or out of regular usage) on Dec 31 @ 23:59, and re-set the id to 0 via a once-a-year SP? Why make things complicated?

Alternatively have a "year" table with a column called "next id" with a value which starts at 0 and which has multiple "future-dated" records lined up before EOY.

barnabeck
Posting Yak Master

236 Posts

Posted - 2009-10-21 : 03:19:55

Why do I make things complicated? Well, I guess I won't be in the office on Dec 31 @ 23:59 nor will I be in the following weeks. As this anual ID is a portion of a self generated customer support number I have to solve it... maybe it's easier to write a "Job" that is executed by schedule on Dec 31.
I did not understand the alternative way with the "year" table?!

Thanks anyway.
Martin

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-10-21 : 07:02:07

yeartable
year offset
2008 19548 <- this will end up as the highest id for that year
2009 15476
2010 0
2011 0
2012 etc

select offset from yeartable
where year = yearportion of getdate()

update yeartable
set offset = offset + 1
where year = yearportion of getdate()

use the offset from the select as your new id on the saved record.

re being in the office....
you could put in some jobs to automatically do this, i.e stop user input, run your job, re-start user input. always useful to be able to suspend/reengage user input in a controlled manner, while still having the databse available for housejkkeeping activities.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources