Balancing the work among users of a group
-
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.
-
I think the reason why you've gotten no help is because you've not provided any readily consumable data. Please see the link in my signature below for one way to provide such a thing in lieu of a graphic of a spreadsheet.
Others wise, my suggestion would be to do a search for "Bin Fill in SQL Server".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
For these sorts of problems, I just allocate according to whatever formula I would ordinarily use to allocate amongst the recipients of the allocations. Then if they do not allocate evenly because of rounding errors, I then remove individual allocations to enough individual recipients of allocations (most situations I just use the last recipient of allocations) by first removing the initial allocation from one or more recipients, summing the remaining allocations, and then just reallocating the remainder to the recipient that I previously had removed the initial allocation from. This allows you to allocate in such away that the sum of the allocations will again add back up to the entirety of items to allocate. Obviously this assumes the units of allocations are equal to or greater than the allocation recipients. This step can be taken even if it reallocates an identical amount due to the situations that allocations can be assigned without producing rounding errors.
heh I really do roll like that but my post is most likely even more indecipherable than OP's LOLOL
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply