How to weekly generate dynamic ID

Question

I want Date wise dynamically generate course ID Example: Last week generate end Batch ID: CSE100CSE101....CSE199 Now This week Start date Dynamically(Automatically) generate Batch ID:CSE200 Thanks..

Wim Sturkenboom · Answer

Assuming you want a (MS) SQL solution declare @lastweek int declare @currweek int declare @lastid int declare @newid varchar(50) -- get weeknumber of last insert set @lastweek = (select DATEPART (wk (select top(1) BatchDate from tblBatch order by BatchDate desc))) -- get current week number set @currweek = (select DATEPART (wk CURRENT_TIMESTAMP)) if @currweek > @lastweek begin -- get the last inserted id set @lastid = (select top (1) SUBSTRING(BatchID LEN(BatchName)+1 LEN(BatchID) -3) from tblBatch order by BatchDate desc) -- create new id set @newid = (select top(1) BatchName from tblBatch order by BatchDate desc) + CAST(@lastid + 1 as varchar(50)) end select @lastweek @currweek @lastid @newid You can put it in a stored procedure and let it return newid. Note that you need to add a check for the end of the year where last week (52/53) will be larger than currweek. Also note that newID will be null if the current week is less than or equal to lastweek. I hope this is more or less what you need and get you on the way.

Nirmal KumarC · Answer

Dear Sir Your Answer is very helpful. Thank you

Nirmal KumarC · Answer

Sorry i am mention CSE201 rong dynamic generate Batch Id CSE200 this id me want. Example: My concept: last week My Batch last Batch ID Table design Batchid BatchName BatchDate Active CSE199 CSE 13 oct 2014 A But I want This Week Start 15 oct 2014 automatically generate batch ID CSE200 Thanks..

Wim Sturkenboom · Answer

Why CSE201 and not CSE200? Next you don't know when the last ID was generated so you can't predict if the next id is still in the same week (what would the number be) or in the next week (see question above). Can you please elaborate more?