Student no. increment

[aishapot]

I want to have an alternative way to increment my student no.
the current way I'm doing to is in my database, mssql. there's a property called Identity Seed(201200001 - my studentid format) and Identity Increment (1 - no. of increments).

The 2012-is the school year, while the 00001- is the no of the student.
what can i do for next school year and upcoming years, the student no would have to be 201300001 but for that to happen the student no must reach 201299999 but that no of students enrolling for a school year is impossible.

can i do this by code instead?

[adam_k]

You try to alter the table and set identity to seed from a higher number - I haven't tried it but I'm betting it will work.

The easiest and most robust way to do it, IMO, is to create a function that will seed for you. It can be something like

select @new_id = convert(char(4),@year) + convert(char(5),max(right(id,5)))
from table where left(id,4) = @year

I think the vars and fields are self-explanatory.

PS: If you're going to use a function or some other way to increment you id then you need to alter the table and get rid of identity. Otherwise you'll have to set identity_insert on & off for this table.

[Reverend Jim]

You can select the current maximum studentID for a given year by

select MAX(StudentID) from Table1 where StudentID between 201100001 and 201199999

Once you have that you can split it into yearpart and idpart by

yearpart = result \ 100000
idpart = result mod 100000

next studentID is 100000*yearpart + idpart + 1

If you are only interested in the current year then you can generate the next idpart directly by

select nextID=1+MAX(StudentID)%100000 from Table1

[Reverend Jim]

Or you can do it adam's way :)