Custom numbering field based on year

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

General SQL Server Forums

New to SQL Server Programming

Custom numbering field based on year

Author

Topic

yiotaa
Starting Member

4 Posts

Posted - 2012-06-14 : 02:53:07

Hi! I am trying to create a numbering field in a table but i want to be reset every year.
I would like to have this format yyyy/00000
e.g
2010/00001
2010/00002
2010/00003
2011/00001
2011/00002
2012/00001
2012/00002
2012/00003

Can you please help me????????????????????

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-14 : 03:05:19

[code]
row_number() over (partition by [year column] order by [some column])
[/code]

KH
[spoiler]Time is always against us[/spoiler]

yiotaa
Starting Member

4 Posts

Posted - 2012-06-14 : 03:25:55

Can you please explain me with more details what you mean?

yiotaa
Starting Member

4 Posts

Posted - 2012-06-14 : 03:35:05

I am using this trigger
REATE TRIGGER insertReceiptNoLong ON dbo.RECEIPTS
after INSERT
AS
Declare
@longID varchar(50),
@idLength int,
@seedValue varchar(5)

--Check if the seed is 5 digits long
set @idLength = 5 - (select len(convert(varchar,@@IDENTITY)))
set @seedValue = convert(varchar,@@IDENTITY)
--if the seed length is less than file padd extra zeroes on left side
while @idLength > 0
Begin
set @seedValue = '0' + @seedValue
set @idLength = @idLength - 1
End

--join everything together
set @longID = convert(varchar,year(getdate())) + '/'+ @seedValue
update RECEIPTS set ReceiptNoLong = @longID where RECEIPTNo = @@IDENTITY

The RECEIPTNo is an autonumbering filed in the table RECEIPTS.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-14 : 03:46:08

Do you need to store that in your table or just need to generate a running number during retrieval ?

KH
[spoiler]Time is always against us[/spoiler]

yiotaa
Starting Member

4 Posts

Posted - 2012-06-14 : 08:24:09

i want to store it in the field ReceiptNoLong

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-14 : 09:12:03