How to reset identity column value on date change in sql ser

Question

i am creating query for hospital patient opd

i want that opd no shuld be reset and start with 1 on date change like

Date opdNo

20-01-2016 01

20-01-2016 02

20-01-2016 03

20-01-2016 04

20-01-2016 05

21-01-2016 01

21-01-2016 02

21-01-2016 03

22-01-2016 01

22-01-2016 02

Is it possiable in sql ??

please help

Thanks

Manas Mohapatra · Answer

Follow below solutions: Option 1 : Create a scheduler/job which will reset the identity column value. It will run everyday at 12:00 AM which will execute the below SQL query: DBCC CHECKIDENT( ' TableName'  RESEED 0 ) Option 2 : You can do it manually: Get no of records as per today date and increase its value by 1. See below SQL query Declare @temp INT; SELECT @temp = Count(ID) + 1 FROm tblName WHERE Date = GetDate(); Now you can use @temp variable to update identity column value.

Raja T · Answer

Hi Anil It is possible but do one job /schedule. That job is doing below steps 1 . Every day take backup and original data move to table tbl_bup_op. 2. Truncate table data in original table. It is automatically identity column start wit 1 only for every day. I hope it is resolve the for your requirement.