How to assign tasks on a Round Robin using visual studio.

Question

Hi all. I have Approval officer table and Licence table I need to assign each applied licence to an approval officer for approval using round robin. Please assist. I am using asp.net visual studio 2013 and SQL 2014.

Nitin Sontakke · Answer

I think you are going to need the third table as well which will keep track of assigned licences. LicenceApproval --------------- LicenceId EmployeeId AssignedDateTime ApprovedDateTime The following query will give you the EmployeeId of staff member having least amount of licences assigned for approval select EmployeeId count(1) from LicenceApproval la where ApprovedDateTime is null group by EmployeeId order by 2 desc

Nitin Sontakke · Answer

Got it. Try select EmployeeId isnull(RecordCount 0) from StaffMember sm left outer join ( select EmployeeId count(1) RecordCount from LicenceApproval where ApprovedDateTime is null group by EmployeeId ) la on sm.EmployeeId = la.EmployeeId order by 2 desc

Sbonga Mathobela · Answer

I now created the third table you suggested and I cn get all IDs for approval Officers i then fail to assign randomly to each approval officer. It keeps assigning to the 1st approval officer.

Sbonga Mathobela · Answer

Here are the tables: Staff Member LicenceTable employeeID licenceID FirstName LicenceType email LicenceStatus The license status is pending by default until its approved by an approval officer. So what i want is that when a user applies for a licence that licence must be assigned to approval officers for approval. if we have 2 approval officers and 4 licences then the output should be 2 licences per approval officer. Thanks

Nitin Sontakke · Answer

Please provide table names column names etc. Which column indicates licence is pending for approval. Which column indicates the sequence of approval officer in which assingment should happen etc.