How to maintain serial number for duplicate id in a table

Question

lets consider i have a table like this create table #temp ( Id int primary key identity(11) amount decimal(100) inserted_date datetime empId int series_no int ) when i will insert record with empId 1000 then i want to insert serial no 123 for that employee id. again if employee id 1001 then serial number start with 123 so on.. how could i achieve this any suggestion?

Manas Mohapatra · Answer

DECLARE @SerialNo INT; SELECT TOP 1 @ SerialNo = ISNULL(empid 0) + 1 FROM #temp WHERE empid = 1 ORDER BY SerialNo DESC; INSERT INTO #temp( amount inserted_date empId series_no) SELECT @amount @date @empid @ SerialNo ; Here @amount @date @empid are declared variable you have to change as per your requirement. Any doubt describe details..

Manas Mohapatra · Answer

Choose which scenario do you want. And please describe more to better understand. empid serialno -------- --------- 1000 1 1000 2 1000 3 or empid serialno -------- --------- 1000 123

Amol Jadhao · Answer

first senario.