none
Need help with % work allocation

    Question

  • Hi Team,

    I need a help with the query to get % allocation of consumers to the users within a group.

    There are groups under the group there are users and consumers should be allocated based on the % work to be allocated.

    Details:

    Group and Users in that group (an user may be in different and work allocation should be within users of that group)

    A Group has users and it has % of allocation:

    New consumers coming into the system:

    Those to be allocated to the users based on the % for that group and user.

    There is no hard code rule that % has to be accurate but if they are more or less matching then it should be fine.

    % allocation based on new consumers be like:


    • Group1 User1 = 25% of 4 (new consumers) = 1 so 1 consumer should be allocated to him
    • Group1 User2 = 15% of 4 = 0.6 and it should be allocated with 1 consumer as it is more 0.5
    • Group1 User3 = 10% of 4 = 0.4 so it won't the consumer
    • Group1 User4 = 50%4 = 2 so it should get 2 consumers
    • Group2 User5 = 0.6 = 1 consumer
    • Group2 User1 = 1.2 = 1 consumer
    • Group2 User6 = 1.98 = 2 consumers
    • Group2 User7 = 2.22 = 2 consumers
    • Group3 User2 = 2.2 = 2 consumers + 1 consumer for the reminder as it is first user or order and user1 may get
    • Group3 User1 = 2.2 = 2 consumers 
    • Group3 User8 = 2.2 = 2 consumers 
    • Group3 User9 = 2.2 = 2 consumers 
    • Group3 User10 = 2.2 = 2 consumers 

    Querys:

    DECLARE @GroupUser TABLE
    (
    	GroupName VARCHAR(10),
    	UserName VARCHAR(10),
    	Percentage DECIMAL(4,2)
    )
    INSERT INTO @GroupUser
    SELECT 'Group1','User1',25 UNION ALL
    SELECT 'Group1','User2',15 UNION ALL
    SELECT 'Group1','User3',10 UNION ALL
    SELECT 'Group1','User4',50 UNION ALL
    SELECT 'Group2','User5',10 UNION ALL
    SELECT 'Group2','User1',20 UNION ALL
    SELECT 'Group2','User6',33 UNION ALL
    SELECT 'Group2','User7',37 UNION ALL
    SELECT 'Group3','User2',20 UNION ALL
    SELECT 'Group3','User1',20 UNION ALL
    SELECT 'Group3','User8',20 UNION ALL
    SELECT 'Group3','User9',20 UNION ALL
    SELECT 'Group3','User10',20
    
    SELECT *FROM @GroupUser
    
    DECLARE @NewWork TABLE
    (
    	GroupName VARCHAR(10),
    	ConsumerNumber INT
    )
    INSERT INTO @NewWork(GroupName,ConsumerNumber)
    SELECT 'Group1',23 UNION ALL
    SELECT 'Group1',32 UNION ALL
    SELECT 'Group1',31 UNION ALL
    SELECT 'Group1',55 UNION ALL
    SELECT 'Group2',100 UNION ALL
    SELECT 'Group2',101 UNION ALL
    SELECT 'Group2',102 UNION ALL
    SELECT 'Group2',103 UNION ALL
    SELECT 'Group2',104 UNION ALL
    SELECT 'Group2',105 UNION ALL
    SELECT 'Group3',106 UNION ALL
    SELECT 'Group3',107 UNION ALL
    SELECT 'Group3',108 UNION ALL
    SELECT 'Group3',110 UNION ALL
    SELECT 'Group3',115 UNION ALL
    SELECT 'Group3',130 UNION ALL
    SELECT 'Group3',243 UNION ALL
    SELECT 'Group3',2 UNION ALL
    SELECT 'Group3',44 UNION ALL
    SELECT 'Group3',67 UNION ALL
    SELECT 'Group3',78
    
    SELECT *FROM @NewWork
    
    DECLARE @FinalExpectedOuput TABLE
    (
    	GroupName VARCHAR(10),
    	ConsumerNumber INT,
    	UserName VARCHAR(10)
    )
    INSERT INTO @FinalExpectedOuput
    SELECT 'Group1',23,'User1' UNION ALL
    SELECT 'Group1',32,'User2' UNION ALL
    SELECT 'Group1',31,'User4' UNION ALL
    SELECT 'Group1',55,'User4' UNION ALL
    SELECT 'Group2',100,'User5' UNION ALL
    SELECT 'Group2',101,'User1' UNION ALL
    SELECT 'Group2',102,'User6' UNION ALL
    SELECT 'Group2',103,'User6' UNION ALL
    SELECT 'Group2',104,'User7' UNION ALL
    SELECT 'Group2',105,'User7' UNION ALL
    SELECT 'Group3',106,'User2' UNION ALL
    SELECT 'Group3',107,'User2' UNION ALL
    SELECT 'Group3',108,'User1' UNION ALL
    SELECT 'Group3',110,'User1' UNION ALL
    SELECT 'Group3',115,'User8' UNION ALL
    SELECT 'Group3',130,'User8' UNION ALL
    SELECT 'Group3',243,'User9' UNION ALL
    SELECT 'Group3',2,'User9' UNION ALL
    SELECT 'Group3',44,'User10' UNION ALL
    SELECT 'Group3',67,'User10' UNION ALL
    SELECT 'Group3',78,'User2'
    
    SELECT *FROM @FinalExpectedOuput

    Appreciate your input

    Regards,

    Eshwar.


    Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

    Friday, April 19, 2019 10:43 AM

All replies

  • I'm a little confused when you talk about new work? In that case, shouldn't we consider current work? Then again, the problem is quite difficult anyway. I am not sure that my solution below meets your requirements exactly, but it appears to match the expected results in your sample.

    There are two CTEs, the first computes the accumulated percentage for the group, starting with the user with highest percentage. I also compute PrevPerc which is the value to to the previous user. The second CTE computes number of consumer in each group and numbers the consumers.

    In the final join, I match on internal. The rowno divided by the count falls into a interval that defines a user in a group. You may note that I divide the percentages by 100 and I cast all values to float to avoid surprises with results having too few decimals.

    ; WITH GroupAccums AS (
        SELECT GroupName, UserName,
               cast(SUM(Percentage) OVER(PARTITION BY GroupName
                                         ORDER BY Percentage DESC, UserName
                                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS float) / 100 AS AccumPerc,
               isnull(cast(SUM(Percentage) OVER(PARTITION BY GroupName
                                                ORDER BY Percentage DESC, UserName
                                                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS float) / 100, 0) AS PrevPerc
        FROM   @GroupUser
    ), ConsumerData AS (
       SELECT GroupName, ConsumerNumber,
              cast(COUNT(*) OVER (PARTITION BY GroupName) AS float) AS GroupCnt,
              cast(row_number() OVER(PARTITION BY GroupName ORDER BY ConsumerNumber) AS float) AS rowno
       FROM   @NewWork
    )
    SELECT GA.GroupName, CD.ConsumerNumber, GA.UserName
    FROM   GroupAccums GA
    JOIN   ConsumerData CD ON CD.GroupName = GA.GroupName
                          AND CD.rowno / CD.GroupCnt > GA.PrevPerc 
                          AND CD.rowno / CD.GroupCnt <= GA.AccumPerc
    ORDER  BY GA.GroupName, CD.ConsumerNumber    


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, April 19, 2019 6:03 PM
  • Sorry for the confusion no need to consider current work it is only new consumers coming into system should be divided purely based on percentage allocated for that user in that group.

    The query is not giving expected results:

    Group1:

    SELECT 'Group1',31,'User4' UNION ALL
    SELECT 'Group1',55,'User4' UNION ALL
    SELECT 'Group1',23,'User1' UNION ALL
    SELECT 'Group1',32,'User2'

    But query is returning User3 which is not expected. (User2 is 15 % (15%4 (consumers) = 0.6) while User3 is 10% (10%4 = 0.4))

    Can you please try with this data?

    DECLARE @GroupUser TABLE
    (
    	GroupName VARCHAR(10),
    	UserName VARCHAR(10),
    	Percentage DECIMAL(4,2)
    )
    INSERT INTO @GroupUser
    SELECT 'Group1','User1',25 UNION ALL
    SELECT 'Group1','User2',15 UNION ALL
    SELECT 'Group1','User3',10 UNION ALL
    SELECT 'Group1','User4',50 UNION ALL
    SELECT 'Group2','User5',10 UNION ALL
    SELECT 'Group2','User1',20 UNION ALL
    SELECT 'Group2','User6',33 UNION ALL
    SELECT 'Group2','User7',37 UNION ALL
    SELECT 'Group3','User2',20 UNION ALL
    SELECT 'Group3','User1',20 UNION ALL
    SELECT 'Group3','User8',20 UNION ALL
    SELECT 'Group3','User9',20 UNION ALL
    SELECT 'Group3','User10',20 UNION ALL
    SELECT 'Group4','User5',25 UNION ALL
    SELECT 'Group4','User7',50 UNION ALL
    SELECT 'Group4','User9',25
    
    --SELECT *FROM @GroupUser
    
    DECLARE @Work TABLE
    (
    	GroupName VARCHAR(10),
    	ConsumerNumber INT
    )
    INSERT INTO @Work(GroupName,ConsumerNumber)
    SELECT 'Group1',23 UNION ALL
    SELECT 'Group1',32 UNION ALL
    SELECT 'Group1',31 UNION ALL
    SELECT 'Group1',55 UNION ALL
    SELECT 'Group2',100 UNION ALL
    SELECT 'Group2',101 UNION ALL
    SELECT 'Group2',102 UNION ALL
    SELECT 'Group2',103 UNION ALL
    SELECT 'Group2',104 UNION ALL
    SELECT 'Group2',105 UNION ALL
    SELECT 'Group3',106 UNION ALL
    SELECT 'Group3',107 UNION ALL
    SELECT 'Group3',108 UNION ALL
    SELECT 'Group3',110 UNION ALL
    SELECT 'Group3',115 UNION ALL
    SELECT 'Group3',130 UNION ALL
    SELECT 'Group3',243 UNION ALL
    SELECT 'Group3',2 UNION ALL
    SELECT 'Group3',44 UNION ALL
    SELECT 'Group3',67 UNION ALL
    SELECT 'Group3',78 UNION ALL
    SELECT 'Group4',200 UNION ALL
    SELECT 'Group4',201 UNION ALL
    SELECT 'Group4',202 UNION ALL
    SELECT 'Group4',203 UNION ALL
    SELECT 'Group4',204 UNION ALL
    SELECT 'Group4',205 UNION ALL
    SELECT 'Group4',206 UNION ALL
    SELECT 'Group4',207 UNION ALL
    SELECT 'Group4',208 UNION ALL
    SELECT 'Group4',209 UNION ALL
    SELECT 'Group4',210
    
    --SELECT *FROM @Work
    
    DECLARE @FinalExpectedOuput TABLE
    (
    	GroupName VARCHAR(10),
    	ConsumerNumber INT,
    	UserName VARCHAR(10)
    )
    INSERT INTO @FinalExpectedOuput
    SELECT 'Group1',23,'User1' UNION ALL
    SELECT 'Group1',32,'User2' UNION ALL
    SELECT 'Group1',31,'User4' UNION ALL
    SELECT 'Group1',55,'User4' UNION ALL
    SELECT 'Group2',100,'User5' UNION ALL
    SELECT 'Group2',101,'User1' UNION ALL
    SELECT 'Group2',102,'User6' UNION ALL
    SELECT 'Group2',103,'User6' UNION ALL
    SELECT 'Group2',104,'User7' UNION ALL
    SELECT 'Group2',105,'User7' UNION ALL
    SELECT 'Group3',106,'User2' UNION ALL
    SELECT 'Group3',107,'User2' UNION ALL
    SELECT 'Group3',108,'User1' UNION ALL
    SELECT 'Group3',110,'User1' UNION ALL
    SELECT 'Group3',115,'User8' UNION ALL
    SELECT 'Group3',130,'User8' UNION ALL
    SELECT 'Group3',243,'User9' UNION ALL
    SELECT 'Group3',2,'User9' UNION ALL
    SELECT 'Group3',44,'User10' UNION ALL
    SELECT 'Group3',67,'User10' UNION ALL
    SELECT 'Group3',78,'User2' UNION ALL
    SELECT 'Group4',200,'User7' UNION ALL
    SELECT 'Group4',201,'User7' UNION ALL
    SELECT 'Group4',202,'User7' UNION ALL
    SELECT 'Group4',203,'User7' UNION ALL
    SELECT 'Group4',204,'User7' UNION ALL
    SELECT 'Group4',205,'User5' UNION ALL
    SELECT 'Group4',206,'User5' UNION ALL
    SELECT 'Group4',207,'User5' UNION ALL
    SELECT 'Group4',208,'User9' UNION ALL
    SELECT 'Group4',209,'User9' UNION ALL
    SELECT 'Group4',210,'User9'
    
    --SELECT *FROM @FinalExpectedOuput

    Thanks,

    Eshwar.


    Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

    Saturday, April 20, 2019 1:04 AM
  • Erland,

    Thanks for you reply!

    The query is mostly working except for Group1 expected is User2 but it is returning User3.

    The work (consumers) should be allocated to users based on their percentages.

    Please let me know if you need any other clarification

    Regards,

    Eshwar.


    • Edited by Eswararao C Saturday, April 20, 2019 3:56 AM
    Saturday, April 20, 2019 3:56 AM
  • I see that the outcome is less than perfect.

    But before we dig too much into trying to improve this, I have a question. In your real-life scenario, how many consumers to you expect per group? I haven't tested, but I like to believe that my solution will work better if there many, say 25 or so. Things get more sketchy when there is only a handful.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, April 20, 2019 12:42 PM
  • Erland,

    Thanks for your reply!

    Depending on the group in a day there may be less (5) or more (10000) not sure for now.

    Yes your solution working in most scenario's, but in this specific scenario Group1 User2 has 15% and (15*4(Total consumers))/100 = 0.6 and User3 has 10% and (10%4)/100 = 0.4 so ideally User2 should be considered more than User3 and it is very much real scenario.

    I am not sure if I am missing something not so good with CTE's not able to figure out how to correct the CTE.

    Appreciate your help

    Regards,

    Eshwar.

    • Edited by Eswararao C Saturday, April 20, 2019 1:00 PM
    Saturday, April 20, 2019 12:59 PM
  • I would suggest this. My solution can run into problems every time there is a group with a user than has p % in allocation, and there are less than 100 / p consumers. For instance, in group 1, there is a user with 10 % allocation, so we would need at least 10 consumer to get a good allocation.

    You can add these columns to the final SELECT list, to get a better understanding of what is happening:

    , CD.rowno / CD.GroupCnt, GA.PrevPerc, GA.AccumPerc

    For group 1, we have the rations 0.25, 0.50, 0.75 and 1. One observation of this is that the user with the smallest allocation, will always get a consumer, which of course a little silly. Then again, this only occurs for very low numbers of consumers.

    I played with subtracting 1 from the row_number for the consumers, so that I get 0, 0.25, 0.50 and 0.75 instead. This resolves the problem with the user with the lowest allocation always getting getting a consumer, but you will find other oddities in the result. And, again, this is due to the low number of consumers.

    This adjusted solution is below. I will have to admit that at this point I am not inclined to resarch this further. Had I known the actual business problem and fully understood that the allocation must be good also for small amounts of consumers, maybe I would have cared. But at this point, I'm tempted to say that this is good enough and you will have to accept rough edges when the number of consumers is low.

    ; WITH GroupAccums AS (
        SELECT GroupName, UserName,
               cast(SUM(Percentage) OVER(PARTITION BY GroupName
                                         ORDER BY Percentage DESC, UserName
                                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS float) / 100 AS AccumPerc,
               isnull(cast(SUM(Percentage) OVER(PARTITION BY GroupName
                                                ORDER BY Percentage DESC, UserName
                                                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS float) / 100, 0) AS PrevPerc
        FROM   @GroupUser
    ), ConsumerData AS (
       SELECT GroupName, ConsumerNumber,
              cast(COUNT(*) OVER (PARTITION BY GroupName) AS float) AS GroupCnt,
              cast(row_number() OVER(PARTITION BY GroupName ORDER BY ConsumerNumber) - 1 AS float) AS rowno
       FROM   @NewWork
    )
    SELECT GA.GroupName, CD.ConsumerNumber, GA.UserName, CD.rowno / CD.GroupCnt, GA.PrevPerc, GA.AccumPerc
    FROM   GroupAccums GA
    JOIN   ConsumerData CD ON CD.GroupName = GA.GroupName
                          AND CD.rowno / CD.GroupCnt >= GA.PrevPerc                       AND CD.rowno / CD.GroupCnt < GA.AccumPerc
    ORDER  BY GA.GroupName, CD.ConsumerNumber


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, April 20, 2019 8:10 PM
  • Hi Eswararao C,

     

    Or would you like this one ?

     
    DECLARE @GroupUser TABLE
    (
    	GroupName VARCHAR(10),
    	UserName VARCHAR(10),
    	Percentage DECIMAL(4,2)
    )
    INSERT INTO @GroupUser
    SELECT 'Group1','User1',25 UNION ALL
    SELECT 'Group1','User2',15 UNION ALL
    SELECT 'Group1','User3',10 UNION ALL
    SELECT 'Group1','User4',50 UNION ALL
    SELECT 'Group2','User5',10 UNION ALL
    SELECT 'Group2','User1',20 UNION ALL
    SELECT 'Group2','User6',33 UNION ALL
    SELECT 'Group2','User7',37 UNION ALL
    SELECT 'Group3','User2',20 UNION ALL
    SELECT 'Group3','User1',20 UNION ALL
    SELECT 'Group3','User8',20 UNION ALL
    SELECT 'Group3','User9',20 UNION ALL
    SELECT 'Group3','User10',20 UNION ALL
    SELECT 'Group4','User5',25 UNION ALL
    SELECT 'Group4','User7',50 UNION ALL
    SELECT 'Group4','User9',25
    
    --SELECT *FROM @GroupUser
    
    DECLARE @NewWork TABLE
    (
    	GroupName VARCHAR(10),
    	ConsumerNumber INT
    )
    INSERT INTO @NewWork(GroupName,ConsumerNumber)
    SELECT 'Group1',23 UNION ALL
    SELECT 'Group1',32 UNION ALL
    SELECT 'Group1',31 UNION ALL
    SELECT 'Group1',55 UNION ALL
    SELECT 'Group2',100 UNION ALL
    SELECT 'Group2',101 UNION ALL
    SELECT 'Group2',102 UNION ALL
    SELECT 'Group2',103 UNION ALL
    SELECT 'Group2',104 UNION ALL
    SELECT 'Group2',105 UNION ALL
    SELECT 'Group3',106 UNION ALL
    SELECT 'Group3',107 UNION ALL
    SELECT 'Group3',108 UNION ALL
    SELECT 'Group3',110 UNION ALL
    SELECT 'Group3',115 UNION ALL
    SELECT 'Group3',130 UNION ALL
    SELECT 'Group3',243 UNION ALL
    SELECT 'Group3',2 UNION ALL
    SELECT 'Group3',44 UNION ALL
    SELECT 'Group3',67 UNION ALL
    SELECT 'Group3',78 UNION ALL
    SELECT 'Group4',200 UNION ALL
    SELECT 'Group4',201 UNION ALL
    SELECT 'Group4',202 UNION ALL
    SELECT 'Group4',203 UNION ALL
    SELECT 'Group4',204 UNION ALL
    SELECT 'Group4',205 UNION ALL
    SELECT 'Group4',206 UNION ALL
    SELECT 'Group4',207 UNION ALL
    SELECT 'Group4',208 UNION ALL
    SELECT 'Group4',209 UNION ALL
    SELECT 'Group4',210
    
    ;with cte as (
    select GroupName, count(*)as ct  from @NewWork group by GroupName)
    ,cte1 as(
    select a.GroupName,a.UserName,cast(a.Percentage*0.01*ct as decimal(10,0)) as new_value ,
    sum(cast(a.Percentage*0.01*ct as decimal(10,0)))over(partition by a.GroupName)  sum_new_value,
    row_number()over(partition by a.GroupName order by  (select 1 )) as rn ,
    max(Percentage)over(partition by a.GroupName ) as  max_Percentage ,Percentage,
    b.ct 
    from @GroupUser a join cte b on a.GroupName=b.GroupName)
    ,cte2 as (
    select GroupName,UserName,
    case when sum_new_value<ct and rn=1 then new_value+(ct-sum_new_value) 
    when sum_new_value>ct and UserName =(select max(UserName) From cte1 b where a.GroupName=b.GroupName and a.max_Percentage=b.Percentage) then new_value-(sum_new_value-ct) 
    else new_value end new_value,
    row_number()over(partition by GroupName order by (select 1)) as rn
    from cte1 a)
    ,cte3 as (
    select * from cte2 a join (SELECT number FROM master..spt_values WHERE type='p') b 
    on a.new_value>b.number
    )
    select a.GroupName,a.UserName,b.ConsumerNumber from cte3 a join (select *,row_number()over(partition by GroupName order by (select 1)) as rn from @NewWork) b 
    on a.rn=b.rn and a.GroupName=b.GroupName
    /*
    GroupName  UserName   ConsumerNumber
    ---------- ---------- --------------
    Group1     User1      23
    Group1     User2      32
    Group1     User4      55
    Group1     User4      55
    Group2     User5      100
    Group2     User1      101
    Group2     User6      102
    Group2     User6      102
    Group2     User7      103
    Group2     User7      103
    Group3     User2      106
    Group3     User2      106
    Group3     User2      106
    Group3     User1      107
    Group3     User1      107
    Group3     User8      108
    Group3     User8      108
    Group3     User9      110
    Group3     User9      110
    Group3     User10     115
    Group3     User10     115
    Group4     User5      200
    Group4     User5      200
    Group4     User5      200
    Group4     User7      201
    Group4     User7      201
    Group4     User7      201
    Group4     User7      201
    Group4     User7      201
    Group4     User9      202
    Group4     User9      202
    Group4     User9      202
    */
    
    
    

    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 22, 2019 7:40 AM