Hi, I have to split up clients by their last names alphabetically so that each team dealing with clients has about the same workload. When dealing with numbers of clients, the end of one group might be at 447 clients, and the beginning of the next starts with the 478th client. If you round where you expect to end the next group, it will be in one group of the other.
But when weighting people by the amount of work they are expected to be, one group might finish at 447 units of work, and that's really where the next group picks up. As you calculate the people belonging to each team, you calculate where the end of the next team should be based on the end of the current group and how much work you have left to apportion to the remaining groups.
The problem is that you could have that next end-point land exactly on a boundary line. The code shows this happening. There are 63 units of work you want to divide between three teams, so ideally 21 units per team. The first team will get 23 (surnames A and B) which is close to what you want, then the next two teams should ideally get 20 each. That puts you exactly on the boundary between C and D. If you choose the group break using BETWEEN you would pick both groups.
If you pick the group using target >start_point and target <= end_point, you get a recursion error, so the CYCLE clause is necessary, but I don't understand why.
create table test_limits
(letter varchar2(1)
, s number(10,2)
, e number(10,2));
insert into test_limits values ('A',0,11);
insert into test_limits values ('B',11,23);
insert into test_limits values ('C',23,43);
insert into test_limits values ('D',43,47);
insert into test_limits values ('E',47,55);
insert into test_limits values ('F',55,63);
create view letter_view as
select letter,s,e,
lead(letter) over (order by letter) leadletter
,lag(letter) over (order by letter) lagletter
from test_limits;
with num_weight as
(select max(e) nrows, 3 numgrps
from test_limits
)
,grpsw(grp,grpstart,grpend,nextstart, endrow, nextend, total_rows, numgrps) as
(select grp,grpstart
,case when abs(target- s) > abs(target - e) then letter else lagletter end grpend -- end of the group we chose as the last in this team
,case when abs(target- s) > abs(target - e) then leadletter else lagletter end followstart -- beginning of the group after the group we chose
,case when abs(target- s) > abs(target - e) then e else s -1 end endrow -- row number corresponding to the end of this group
,case when abs(target- s) > abs(target - e) -- is where the next team should end ideally
then e else s end + (total_rows-case when abs(target- s) > abs(target - e) then e else s end)/(numgrps - 0 - 1) nextend
, total_rows
, numgrps
from
(select 1 grp,'A' grpstart
, letter
, lagletter
, leadletter
, n.nrows/n.numgrps target
, s -- first row
, e -- last_row
, n.nrows total_rows
, n.numgrps
from letter_view
cross join num_weight n
where n.nrows/n.numgrps between s and e
)
UNION ALL
select g.grp+1,g.nextstart grpstart
,case when abs(g.nextend - s) > abs(g.nextend - e) then letter else lagletter end grpend -- end of the group we chose as the last in this team
,case when abs(g.nextend - s) > abs(g.nextend - e) then leadletter else letter end followstart -- closer to last end of this sublast, else pre sublast
,case when abs(g.nextend - s) > abs(g.nextend - e) then e else s end endrow -- row number corresponding to the end of this team
,case when g.grp = g.numgrps - 1 then total_rows else
case when abs(g.nextend - s) > abs(g.nextend - e)
then e else s end + (total_rows-case when abs(g.nextend - s) > abs(g.nextend - e) then e else s end)/(g.numgrps-g.grp-1)
end nextend
, total_rows
, g.numgrps
from letter_view sl
join grpsw g on g.nextend > sl.s and g.nextend <= sl.e
where g.grp < g.numgrps
)
CYCLE grp SET is_loop TO 'Y' DEFAULT 'N'
select *
from grpsw