Need help with sequential number generation that resets based on date

[videostar]

Greetings,
first post, but have read many times for various CF questions.

My goal is, using CF and MS-SQL 2k, the following:

[I already have primary key for this table] and -

I need to have either CF or MS-SQL upon a record insert, generate a sequential 3 digit number, that is incremented by 1.

However, this number needs to check the date, as if it is the first of the month, it should reset the counter back to one. [Each record will have other unique fields, like primary key, and a datetime entry, so I am not worried about duplication]

Essentially this is a "Job #" field.

I am not too sure the best/correct way to accomplish this goal-- MSSQL or CF? From Stored Procedures, Sequences, to various "Select MAX" statements... [and yes, googled a tremendous amount the past few days]

I appreciate any help/suggestions this forum can provide
--Andrew

[1harsh789]

I think the best method can be as follow.

Before inserting the new row, you must match the last inserted row with the current day. And if it is changing reset the counter back to 1.

Algorithm can be something like

SELECT day,counter FROM last inserted row
if (cur_day==1 AND cur_day<>day)
  new_counter = 1
else
  new_counter=counter+1