SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with Idea on Round Robin TSQL solution


Help with Idea on Round Robin TSQL solution

Author
Message
brad.mason5
brad.mason5
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1586 Visits: 2042
Hi SQLServerCentral,

Here is what I am trying to accomplish and am trying to come up with an approach.

I have a table called “RoundRobin” and this holds the round StaffID and StudentCount.

What I would like to do is, using the current student count per staff member distribute additional students to each staff member based on load.

So for example I would like to redistribute 50 students to the staff members based on load.

What I am hoping to get is a number of students that should be redistributed based on the input number (say 50 in example).

RoundRobinID StudentCount Student New Count Number of new students
1 1 20 19
2 4 20 16
3 5 20 15
4 25 25 0
35 85 50

Any help would be appreciated.

IF OBJECT_ID('dbo.RoundRobin', 'u') IS NOT NULL
DROP TABLE dbo.RoundRobin

create table RoundRobin
(
StaffID INT IDENTITY(1,1) PRIMARY KEY
,StudentCount INT
)

INSERT INTO dbo.RoundRobin
SELECT 5
UNION
SELECT 25
UNION
SELECT 1
UNION
SELECT 4

select *
from dbo.RoundRobin
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14212 Visits: 12197
If the number of new students and the number of staff is always small, a simple loop adding one to whoever has the fewest students until there are no new students left willprobably be good enough.

If there are very large numbers of new students and/or large numbers of staff, you can better performance with a more complex loop: for example the followwing works reasonably well:
declare @newstudents int = 50 ;
declare @take int = 0, @staffid int = 0, @ranksize int = 0;
declare @t table (total int, rankno int, StaffID int primary key) ;
declare @r table (StaffID int , oldStudents int, additions int, total as oldStudents+additions);
insert @r select StaffID, StudentCount, 0 from RoundRobin;
while @newstudents > 0 and @take is not null
begin
insert @t select total, dense_rank() over (order by total), StaffID from @r ;
select top 1 @take = a.total - b.total
from (select * from @t where rankno = 2) as a cross join (select * from @t where rankno = 1) as b ;
select @ranksize = COUNT(*) from @t where rankno = 1;
if not(@ranksize*@take <= @newstudents) set @take = @newstudents/@ranksize ;
if @take > 0
begin
with z as (select R.StaffID, R.oldStudents, R.additions from @r R inner join @t T on R.StaffID = T.staffID where rankno = 1)
update z set additions = additions + @take ;
set @newstudents = @newstudents - @ranksize*@take ;
set @take = 0;
end
else
begin
with z as (select top(@newstudents) R.Staffid, R.oldStudents,R.additions
from @r R inner join @t T on T.StaffID = R.StaffID where rankno = 1
order by oldStudents desc)
update z set additions = additions+1;
set @newstudents = case when @newstudents > @ranksize then @newstudents-@ranksize else 0 end
end
delete @t

end
select * from @r


I can't see any way of doing it without a loop.

Tom

brad.mason5
brad.mason5
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1586 Visits: 2042
WOW this is great!!

HAPPY SATURDAY! Thank you for your time I appreciate it.

Thanks again,
Brad
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search