Custom Identity Column 2

[drewson]

I want to create a custom identity column or at least a way of setting IDs in my table based on the following format:

YYYYDDDXXXXX

YYYY = Four digit year
DDD = the day of the year, in numeric form
XXXXX = an incremental integer that restarts at 00001 every day.

So, on January 1, 2007, I would have a range of 200700100001 that goes up to 200700199999. When January 2, 2007 comes around, the numbers would start over, 200700200001 to 200700299999.

How can I accomplish this in SQL Server 2005?

Thanks!
Drew

 

[ptheriault]

Look up computed columns in Books on-Line.

You could do something like this.

select convert(char(2),day(getdate()))+ convert(char(4),year(getdate()))+ '0001'






- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[aspvbnetnerd]

[COLOR=blue]DECLARE[/color] @YEAR	 [COLOR=blue]VARCHAR[/color](30)
[COLOR=blue]DECLARE[/color] @DAY	  [COLOR=blue]VARCHAR[/color](30)
[COLOR=blue]DECLARE[/color] @VALUE	[COLOR=blue]VARCHAR[/color](30)

[COLOR=blue]SELECT[/color] @YEAR = [COLOR=#FF00FF]YEAR[/color]([COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]CHAR[/color](10),[COLOR=#FF00FF]GETDATE[/color](), 101))
[COLOR=blue]SELECT[/color] @DAY = [COLOR=#FF00FF]DAY[/color]([COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]CHAR[/color](10),[COLOR=#FF00FF]GETDATE[/color](), 101))

[COLOR=blue]IF[/color] LEN(@DAY) = 1
	[COLOR=blue]BEGIN[/color]
		[COLOR=blue]SET[/color] @DAY = [COLOR=red]'00'[/color]+@DAY
	[COLOR=blue]END[/color]
[COLOR=blue]ELSE[/color] [COLOR=blue]IF[/color] LEN(@DAY) = 2
	[COLOR=blue]BEGIN[/color]
		[COLOR=blue]SET[/color] @DAY = [COLOR=red]'0'[/color]+@DAY
	[COLOR=blue]END[/color]
[COLOR=blue]SET[/color] @VALUE = @YEAR+[COLOR=red]''[/color]+@DAY+[COLOR=red]'00000'[/color]
[COLOR=blue]SELECT[/color] @VALUE + [COLOR=#FF00FF]CAST[/color](1 [COLOR=blue]AS[/color] [COLOR=blue]VARCHAR[/color](30))

George

 

[AlexCuse]

aspvbnetnerd - you don't need to look at the length of the day. Try this:

SET @DAY = right('00'+ @DAY, 3)

(you could also work this into your initial setting of @DAY, but I think you'd need to cast the datepart result)

Hope this helps,

Alex



Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Also, after I read initial question, you guys' queries aren't returning the day of the year.

[COLOR=green]--this returns day of MONTH
[/color][COLOR=blue]select[/color] [COLOR=#FF00FF]DAY[/color]([COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]CHAR[/color](10),[COLOR=#FF00FF]GETDATE[/color](), 101))

[COLOR=green]--use this instead
[/color][COLOR=blue]select[/color] [COLOR=#FF00FF]datepart[/color](dy, [COLOR=#FF00FF]getdate[/color]())

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ptheriault]

Good catch Alex,
I just saw day.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

I have revised my query.

[COLOR=blue]select[/color] [COLOR=#FF00FF]convert[/color]([COLOR=blue]char[/color](4),[COLOR=#FF00FF]year[/color]([COLOR=#FF00FF]getdate[/color]()))+ [COLOR=#FF00FF]convert[/color]([COLOR=blue]char[/color](3),[COLOR=#FF00FF]datepart[/color](dy,[COLOR=#FF00FF]getdate[/color]()))+ [COLOR=red]'0001'[/color]

The hard part here is the last 5 digits.



- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[aspvbnetnerd]

AlexCause,
I didn't know about the right function.
I'll try to remember that.

George

 

[AlexCuse]

Here's something to consider:

[COLOR=blue]declare[/color] @lastFour [COLOR=blue]table[/color] (dd [COLOR=blue]char[/color](3), yy [COLOR=blue]int[/color], lastFour [COLOR=blue]char[/color](4))

[COLOR=blue]insert[/color] @lastFour
[COLOR=blue]select[/color] [COLOR=red]'003'[/color], 2007, [COLOR=red]'0001'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'005'[/color], 2007, [COLOR=red]'0003'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'010'[/color], 2007, [COLOR=red]'0015'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'013'[/color], 2007, [COLOR=red]'0115'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'015'[/color], 2007, [COLOR=red]'0999'[/color]


[COLOR=blue]select[/color] dd, yy, [COLOR=#FF00FF]right[/color]([COLOR=red]'000'[/color] 
+ [COLOR=#FF00FF]cast[/color](([COLOR=#FF00FF]coalesce[/color]([COLOR=#FF00FF]max[/color]([COLOR=#FF00FF]cast[/color](lastFour [COLOR=blue]as[/color] [COLOR=blue]int[/color])), 0)+1) [COLOR=blue]as[/color] [COLOR=blue]VarChar[/color])
, 4)
[COLOR=blue]from[/color] @lastFOur 
[COLOR=blue]group[/color] [COLOR=blue]by[/color] dd, yy

Ignorance of certain subjects is a great part of wisdom

 

[ptheriault]

I've come up with this. I don't know if it is the best aproach and you will have to add some logic to reset your number back to 1 at the begining of the day. I'm sure George or Denis have a better way to day this.

[COLOR=blue]declare[/color] @num [COLOR=blue]int[/color]
[COLOR=blue]select[/color] @num = [COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]int[/color],[COLOR=#FF00FF]RIGHT[/color]([COLOR=#FF00FF]convert[/color]([COLOR=blue]char[/color](4),[COLOR=#FF00FF]year[/color]([COLOR=#FF00FF]getdate[/color]()))+ [COLOR=#FF00FF]convert[/color]([COLOR=blue]char[/color](3),[COLOR=#FF00FF]datepart[/color](dy,[COLOR=#FF00FF]getdate[/color]()))+ [COLOR=red]'00001'[/color],5))
[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]convert[/color]([COLOR=blue]char[/color](4),[COLOR=#FF00FF]year[/color]([COLOR=#FF00FF]getdate[/color]()))+ [COLOR=#FF00FF]convert[/color]([COLOR=blue]char[/color](3),[COLOR=#FF00FF]datepart[/color](dy,[COLOR=#FF00FF]getdate[/color]()))+ [COLOR=red]'0000'[/color]+ [COLOR=#FF00FF]convert[/color]([COLOR=blue]varchar[/color](5),@num)
[COLOR=blue]select[/color] @num = [COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]int[/color],[COLOR=#FF00FF]RIGHT[/color]([COLOR=#FF00FF]convert[/color]([COLOR=blue]char[/color](4),[COLOR=#FF00FF]year[/color]([COLOR=#FF00FF]getdate[/color]()))+ [COLOR=#FF00FF]convert[/color]([COLOR=blue]char[/color](3),[COLOR=#FF00FF]datepart[/color](dy,[COLOR=#FF00FF]getdate[/color]()))+ [COLOR=red]'00001'[/color],5))+1
[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]convert[/color]([COLOR=blue]char[/color](4),[COLOR=#FF00FF]year[/color]([COLOR=#FF00FF]getdate[/color]()))+ [COLOR=#FF00FF]convert[/color]([COLOR=blue]char[/color](3),[COLOR=#FF00FF]datepart[/color](dy,[COLOR=#FF00FF]getdate[/color]()))+ [COLOR=red]'0000'[/color]+ [COLOR=#FF00FF]convert[/color]([COLOR=blue]varchar[/color](5),@num)

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[AlexCuse]

Paul - Coalesce is your friend

no need to re set each day:

[COLOR=green]--declare variables
[/color][COLOR=blue]declare[/color] @d [COLOR=blue]char[/color](3)
[COLOR=blue]declare[/color] @yr [COLOR=blue]char[/color](4)
[COLOR=blue]declare[/color] @last4 [COLOR=blue]VarChar[/color](4)

[COLOR=green]--assign values
[/color][COLOR=blue]set[/color] @d = [COLOR=#FF00FF]right[/color]([COLOR=red]'00'[/color] + [COLOR=#FF00FF]cast[/color]([COLOR=#FF00FF]datepart[/color](dy, [COLOR=#FF00FF]getdate[/color]()) [COLOR=blue]as[/color] [COLOR=blue]VarChar[/color]), 3)
[COLOR=blue]set[/color] @yr = [COLOR=#FF00FF]year[/color]([COLOR=#FF00FF]getdate[/color]())
[COLOR=blue]select[/color] @last4 = [COLOR=#FF00FF]coalesce[/color]([COLOR=#FF00FF]max[/color]([COLOR=#FF00FF]cast[/color](lastFour [COLOR=blue]as[/color] [COLOR=blue]int[/color])), 0) + 1 [COLOR=blue]from[/color] @lastFour
	[COLOR=blue]where[/color] dd = @d
	and yy = @yr

[COLOR=green]--the 'id' value
[/color][COLOR=blue]select[/color] @yr + @d + [COLOR=#FF00FF]right[/color]([COLOR=red]'000'[/color] + @last4, 4)

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

I didn't realize that was a cross post ;-)

Also, I didn't realize that I counted the x's wrong in the initial post :-(

Here it is, adjusted to add five characters to the end

[COLOR=green]--declare variables
[/color][COLOR=blue]declare[/color] @d [COLOR=blue]char[/color](3)
[COLOR=blue]declare[/color] @yr [COLOR=blue]char[/color](4)
[COLOR=blue]declare[/color] @last5 [COLOR=blue]VarChar[/color](5)

[COLOR=green]--assign values
[/color][COLOR=blue]set[/color] @d = [COLOR=#FF00FF]right[/color]([COLOR=red]'00'[/color] + [COLOR=#FF00FF]cast[/color]([COLOR=#FF00FF]datepart[/color](dy, [COLOR=#FF00FF]getdate[/color]()) [COLOR=blue]as[/color] [COLOR=blue]VarChar[/color]), 3)
[COLOR=blue]set[/color] @yr = [COLOR=#FF00FF]year[/color]([COLOR=#FF00FF]getdate[/color]())
[COLOR=blue]select[/color] @last5 = [COLOR=#FF00FF]coalesce[/color]([COLOR=#FF00FF]max[/color]([COLOR=#FF00FF]cast[/color]([COLOR=#FF00FF]right[/color](IDCol, 5) [COLOR=blue]as[/color] [COLOR=blue]int[/color])), 0) + 1 [COLOR=blue]from[/color] myTable
	[COLOR=blue]where[/color] dd = @d
	and yy = @yr

[COLOR=green]--the 'id' value
[/color][COLOR=blue]select[/color] @yr + @d + [COLOR=#FF00FF]right[/color]([COLOR=red]'0000'[/color] + @last5, 5)

Ignorance of certain subjects is a great part of wisdom

 

[ptheriault]

Nice job Alex!

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[aspvbnetnerd]

Must say that both Alex and Paul did pretty well

George

 

[drewson]

Thanks everyone for the input. I think this will solve my issue completely. I put it in a stored procedure, and it works great! Although, I considered making it a scalar-valued function. Any recommendations on the best way to implement it?

To take it a step further, I'd like to keep the number from being repeated if the code is called at the same time from two different processes, I don't want both processes to get the same exact number. I'm not sure if I need to use a transaction in the stored procedure or from the calling application, or even if a transaction will accomplish this.

Thanks!

 

[AlexCuse]

search for 'transaction isolation levels SQL Server' on Google, and you will be able to find the one that you can use for your insert to ensure that only one record is inserted at a time.

Post back with any questions.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom