Hi readers!

I have a column (Box Serial Number) with the next format:
YYDDDSSSSS where:
YY - last two digits of year
DDD - Day of year ( 001 - 365)
SSSSS - Consecutive number.

I need a user-defined counter that everyday begins at 1 but changes every time I insert a row in a table. I am new mssql7 user (2 days old) so I think I must use a trigger (insert) to achieve this but I do not how to reset this counter.

Here is my code that build the serial number, but I don´t know how to call any of the ten user-defined counter availables.

Set dateformat dmy
DECLARE @BSN CHAR(12)
SET @BSN =
substring(convert(char,getdate(),121),3,2)+substring(str(datediff(day,convert(datetime,'31/12/'+str(year(getdate())-1,4)),getdate())+1000,4),2,3)+SSSSSS

any kind of suggestion is wellcome

thanks in advance!
Martin Alcaraz
malacaraz@rtn.uson.mx