The idea is to divide and get the rest (modulo) of the scheduler records with total number of teachers per lesson.
CASE WHEN S.rn % TC.noTeachers = 0 THEN TC.noTeachers ELSE S.RN % TC.noTeachers END
I hope this is the desired output
IF OBJECT_ID('tempdb..#Schedule') IS NOT NULL DROP TABLE #Schedule;
IF OBJECT_ID('tempdb..#Teachers') IS NOT NULL DROP TABLE #Teachers;
CREATE TABLE #Schedule
([ID] varchar(4)
, [Lesson] varchar(10)
, [date] datetime
, [time1] time
, [time2] time
, [teacher] char(3))
;
INSERT INTO #Schedule
([ID], [Lesson], [date], [time1], [time2])
VALUES
('M001', 'Math', '2017-01-08 00:00:00', '17:00', '19:00'),
('M002', 'English', '2017-01-08 00:00:00', '13:00', '15:00'),
('M003', 'Design', '2017-01-08 00:00:00', '17:00', '19:00'),
('M001', 'Math', '2017-02-08 00:00:00', '13:00', '15:00'),
('M002', 'English', '2017-02-08 00:00:00', '17:00', '19:00'),
('M003', 'Design', '2017-02-08 00:00:00', '17:00', '19:00'),
('M001', 'Math', '2017-04-08 00:00:00', '13:00', '15:00'),
('M001', 'Math', '2017-03-08 00:00:00', '17:00', '19:00'),
('M001', 'Math', '2017-05-08 00:00:00', '13:00', '15:00'),
('M001', 'Math', '2017-06-08 00:00:00', '17:00', '19:00'),
('M001', 'Math', '2017-07-08 00:00:00', '13:00', '15:00'),
('M001', 'Math', '2017-08-08 00:00:00', '17:00', '19:00'),
('M001', 'Math', '2017-09-08 00:00:00', '13:00', '15:00'),
('M001', 'Math', '2017-10-08 00:00:00', '17:00', '19:00');
CREATE TABLE #Teachers
([ID] char(3)
, [Name] varchar(50)
, [Lesson] varchar(10)
, [Priority] int)
;
INSERT INTO #Teachers
([ID], [Name], [Lesson], [Priority])
VALUES
('001', 'John', 'Math', 1),
('002', 'Mike', 'Math', 2),
('003', 'Clara', 'Math', 3),
('004', 'Noah', 'Design', 1),
('005', 'Jack', 'Design', 2),
('006', 'Kath', 'English', 1),
('007', 'Steve', 'English', 2)
;
WITH cte_S
AS (SELECT [ID], [Lesson], [date], [time1], [time2]
, ROW_NUMBER()OVER(PARTITION BY [Lesson] ORDER BY [date], [time1], [time2] ) AS rn
FROM #Schedule AS S
)
SELECT S.[ID], S.[Lesson], S.[date], S.[time1], S.[time2], T.ID, T.Name
FROM
(SELECT count(ID) as noTeachers , [Lesson] FROM #Teachers AS T GROUP BY [Lesson]) AS TC
INNER JOIN
(SELECT [ID], [Name], [Lesson], [Priority],ROW_NUMBER()OVER(PARTITION BY T.[Lesson] ORDER BY T.[Priority] ) AS rn FROM #Teachers AS T)T
ON TC.Lesson = T.Lesson
CROSS APPLY
(SELECT [ID], [Lesson], [date], [time1], [time2], S.rn
FROM cte_S AS S
WHERE
S.Lesson = T.Lesson
AND CASE WHEN S.rn % TC.noTeachers = 0 THEN TC.noTeachers ELSE S.RN % TC.noTeachers END =T.rn
) AS S
output:
ID Lesson date time1 time2 ID Name
M003 Design 2017-01-08 00:00:00.000 17:00:00.0000000 19:00:00.0000000 004 Noah
M003 Design 2017-02-08 00:00:00.000 17:00:00.0000000 19:00:00.0000000 005 Jack
M002 English 2017-01-08 00:00:00.000 13:00:00.0000000 15:00:00.0000000 006 Kath
M002 English 2017-02-08 00:00:00.000 17:00:00.0000000 19:00:00.0000000 007 Steve
M001 Math 2017-01-08 00:00:00.000 17:00:00.0000000 19:00:00.0000000 001 John
M001 Math 2017-02-08 00:00:00.000 13:00:00.0000000 15:00:00.0000000 002 Mike
M001 Math 2017-03-08 00:00:00.000 17:00:00.0000000 19:00:00.0000000 003 Clara
M001 Math 2017-04-08 00:00:00.000 13:00:00.0000000 15:00:00.0000000 001 John
M001 Math 2017-05-08 00:00:00.000 13:00:00.0000000 15:00:00.0000000 002 Mike
M001 Math 2017-06-08 00:00:00.000 17:00:00.0000000 19:00:00.0000000 003 Clara
M001 Math 2017-07-08 00:00:00.000 13:00:00.0000000 15:00:00.0000000 001 John
M001 Math 2017-08-08 00:00:00.000 17:00:00.0000000 19:00:00.0000000 002 Mike
M001 Math 2017-09-08 00:00:00.000 13:00:00.0000000 15:00:00.0000000 003 Clara
M001 Math 2017-10-08 00:00:00.000 17:00:00.0000000 19:00:00.0000000 001 John