# 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),
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,
)
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```

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 (
cast(SUM(Percentage) OVER(PARTITION BY GroupName
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS float) / 100 AS AccumPerc,
isnull(cast(SUM(Percentage) OVER(PARTITION BY GroupName
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
)
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),
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,
)
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,

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 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,

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.

Regards,

Eshwar.

• Edited by 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 (
cast(SUM(Percentage) OVER(PARTITION BY GroupName
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS float) / 100 AS AccumPerc,
isnull(cast(SUM(Percentage) OVER(PARTITION BY GroupName
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),
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 (
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
/*
---------- ---------- --------------
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
*/

```