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.