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

Sbonga Mathobela

9y1.3k.NET

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.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke9y ago
Accepted 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
+3
Nitin Sontakke9y ago
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
+2
Sbonga Mathobela9y ago
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.
+1
Sbonga Mathobela9y ago
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
+1
Nitin Sontakke9y ago
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.
+1