SQLTeam.com | Weblogs | Forums

Balancing the work among users of a group

#1

Hi Team,

I need a help with the query to get the work balance among users in a group.

There are groups under the group there are users and users will be working on consumers. Query should do balance of the work between the users in that group.

Details: Attached both input and expected outputs

Allocation should happen as below:

For Group1:

  • Current + new = ( 3+10+4+0 + 4 )/4 = 5.25 so everyone should have 5 except first user as 0.25 (reminder) converted to 6, but User2 is not eligible as he already working on 10 consumers so it should be recalculated excluding User2
  • Current excluding User2 + new = ( 3+4+0 + 4)/3 = 3.6666667 so User1, User4 is only eligible so it should be recalculated excluding User3
  • Current excluding User2 and User3 + new = ( 3+0 +4)/2 = 3.5 so User1 should get 1 consumer and User4 should get 3 consumers, the final output User1 got consumer with consumer number = 23 and User4 got the rest of the consumers

For Group2:

  • Current + new = ( 5+6+7+8 + 6)/ 4 = 8 so everyone should get 8 consumers but User7 is already working on 8 consumers so he is not eligible and it should be recalculated
  • Current excluding User7 + new = ( 5+6+7 + 6 )/3 = 8 so everyone should get 8, so in the final output User5 got consumers 100,101,102 (making total tally 8), User1 got 103 and 104, User6 got 105

For Group3:

  • Current + new = ( 10+9+10+11+10 + 11 )/5 = 12.2 so everyone eligible for new consumers, final output User2 got 106,107,108 User1 got 110,115,130 User8 got 243,2 User9 got 44 and User10 got 67,78

Regards,
Eshwar.