Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Other SQL Server Topics (2005)  SQL Coding

Author  Topic  Purple07 Starting Member 1 Post Posted - 2009-10-07 : 22:54:04 i have this code here, which only assign all incoming tickets to the one with the highest quota. I would like to distribute the tickets according to their quota %. For e.g there are 2 staffs. Staff A : 60% Staff B : 40% Staff A : 5 tickets (currently on hand) Staff B : 3 tickets (currently on hand) Now there is an incoming ticket, who should ia assign to??? the algorithm is, staffSupposedToHitQuota = (staffCurrentlyonHandTicket + 1 ) * (quota/100) Using the staffSupposedToHitQuota to compare wif the current tickets they have on hand. If currentTicketsOnHand < SupposedToHitQuota Then ticket submitted to him. Else 'Compare the next staff's supposedToHitQuota and CurrentTicketsOnHAnd If Both staff have hit their quota, the next incoming ticket will b assigned to the one with the highest quota. ------------------------------------------------------------- Public Function CalculateQuota(ByVal TicketCategoryID As Integer, ByVal refDT As DataTable) As Integer Dim i As Integer Dim ArrTotalTicket(refDT.Rows.Count) As Integer Dim ArrStaffQuota(refDT.Rows.Count) As Double For i = 0 To refDT.Rows.Count - 1 ArrTotalTicket(i) = Me.DBSelect_TotalTickets(refDT.Rows(i)("SupportStaffID")) Next i 'begin(formula) ' loop the dt again to count the quota ' assign the value For i = 0 To refDT.Rows.Count - 1 ArrStaffQuota(i) = ((ArrTotalTicket(i) + 1) * (refDT.Rows(i)("TicketQuota") / 100)) Next i 'compare the each of the quota value Dim actualSupportStaffID As Integer = 0 Dim maxQuota As Double For i = 0 To UBound(ArrStaffQuota) - 1 If i = 0 Then maxQuota = ArrStaffQuota(i) actualSupportStaffID = refDT.Rows(i)("SupportStaffID") Else If maxQuota < ArrStaffQuota(i) Then maxQuota = ArrStaffQuota(i) actualSupportStaffID = refDT.Rows(i)("SupportStaffID") End If End If Next ' return the actual support id SupportStaffID = actualSupportStaffID Return actualSupportStaffID End Function