

Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 9:51 PM
Points: 1,066,
Visits: 1,229


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




SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 7:11 PM
Points: 7,920,
Visits: 9,646


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




Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 9:51 PM
Points: 1,066,
Visits: 1,229


WOW this is great!!
HAPPY SATURDAY! Thank you for your time I appreciate it.
Thanks again, Brad



