Determining Procedure

1

I have lesson schedule table :
ID Lesson date time1 time2 Teacher
M001 Math 01-08-2017 17:00 19:00
M002 English 01-08-2017 13:00 15:00
M003 Design 01-08-2017 17:00 19:00
M001 Math 02-08-2017 13:00 15:00
M002 English 02-08-2017 17:00 19:00
M003 Design 02-08-2017 17:00 19:00

I also have teacher table with priority
ID Name Lesson Priority
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

Priority is determine who will be teach at the schedule, but if the schedule already put the teacher the second teacher will be put on the schedule and so on.
I still confused how to make it work..please help..

thx

2

If you could post the desired output, will help.

Anyway, here is version 1

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');
    
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)
; 


SELECT S.[ID], S.[Lesson], S.[date], S.[time1], S.[time2], T.ID, T.Name
FROM
   (SELECT [ID], [Name], [Lesson], [Priority],ROW_NUMBER()OVER(PARTITION BY T.[Lesson] ORDER BY T.[Priority] ) AS rn FROM #Teachers AS T)T
   INNER JOIN (SELECT  [ID], [Lesson], [date], [time1], [time2], ROW_NUMBER()OVER(PARTITION BY [Lesson] ORDER BY [date], [time1], [time2] ) AS rn FROM #Schedule AS S) S
   ON T.lesson = S.Lesson
   AND T.RN = S.RN

Here the 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
3

Dear stepson,
If the schedule is more than the available teacher..what is the best solution?
can it reset into the first priority again? without any double schedule of the same teacher at the same time?
for example from your 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
Next schedule..
M003 Design 2017-01-14 00:00:00.000 17:00:00.0000000 19:00:00.0000000
M003 Design 2017-02-14 00:00:00.000 17:00:00.0000000 19:00:00.0000000
M002 English 2017-01-18 00:00:00.000 13:00:00.0000000 15:00:00.0000000
M002 English 2017-02-18 00:00:00.000 17:00:00.0000000 19:00:00.0000000
M001 Math 2017-01-18 00:00:00.000 17:00:00.0000000 19:00:00.0000000
M001 Math 2017-02-18 00:00:00.000 13:00:00.0000000 15:00:00.0000000
and so on...

thx

4

You have to be more precise, because from here, it is hard to know all your's aspect/variables.

schedule is more than the available teacher

you are referring at?
To be easy , it is the same data input ? if yes, the desired output will be?

5

My desire output will be

Next schedule…
ID Lesson date time1 time2 ID Name
M003 Design 2017-01-14 00:00:00.000 17:00:00.0000000 19:00:00.0000000 004 Noah
M003 Design 2017-02-14 00:00:00.000 17:00:00.0000000 19:00:00.0000000 005 Jack
M002 English 2017-01-18 00:00:00.000 13:00:00.0000000 15:00:00.0000000 006 Kath
M002 English 2017-02-18 00:00:00.000 17:00:00.0000000 19:00:00.0000000 007 Steve
M001 Math 2017-01-18 00:00:00.000 17:00:00.0000000 19:00:00.0000000 001 John
M001 Math 2017-02-18 00:00:00.000 13:00:00.0000000 15:00:00.0000000 002 Mike
so.. on
until the there are no schedule anymore..
actually it has relation my topic before about adding data row..
thx

6

I understand what you mean; Can you populate the scheduler table with more records ? ( to have a more real test data)

7

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
1 Like
8

Great..it's working..thanks a lot..

9

Super , it's my pleasure!

10

Sorry need help again I found same teacher with same date and time1 could you help for the solution? thx

11

It will be much easier , if you post this sample (so we better understand it) and how it must be treated.

12

this is the example

ID Lesson Date Time1 Time2 ID Teacher
M003 Design 2017-01-08 00:00:00.000 17:00:00.0000000 19:00:00.0000000 004 Noah
M001 Math 2017-01-08 00:00:00.000 17:00:00.0000000 19:00:00.0000000 004 Noah
M002 English 2017-01-08 00:00:00.000 13:00:00.0000000 15:00:00.0000000 006 Kath
M003 Design 2017-01-08 00:00:00.000 13:00:00.0000000 15:00:00.0000000 007 Kath

it is happened if the teacher added with another lesson ( one teacher for 2 or more lesson)

thx

13

What about the teachers table ? The same teacher should appear in this table twice, once for Design and once for Math.

14

the teacher table mention twice for different lesson , date and time. the requirement is how to make them scheduled without any duplicate schedule. So the scenario is one teacher cannot teach in one same date and time if there were vacant schedule it will fill with next teacher based on the priority. I just figure out using store procedure of determining the teacher but it is possible?

thanks,

Joe

15 
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'),
    ('M003', 'Design', '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'),
    ('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');
  
   
 
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),
    ('001', 'John','Design', 2),
    ('004', 'Noah', 'Design', 1),
    ('005', 'Alex', 'Design', 3)
   
; 


WITH cte_S
AS (SELECT [ID], [Lesson], [date], [time1], [time2]
		, ROW_NUMBER()OVER(PARTITION BY [Lesson] ORDER BY [date], [time1], [time2] ) AS rn 
		, ROW_NUMBER()OVER(PARTITION BY [date], [time1], [time2]  ORDER BY [Lesson] ) AS rn2 
		FROM #Schedule AS S	
	)
,cteFinal
AS
(
SELECT S.[ID] As sID, S.[Lesson], S.[date], S.[time1], S.[time2], T.ID, T.Name, TC.noTeachers 
        , S.rn as Srn 
        , T.rn AS Trn		
        , ROW_NUMBER()OVER(PARTITION BY S.[Lesson] ORDER BY S.[date], S.[time1], S.[time2] ) AS rn 
	, ROW_NUMBER()OVER(PARTITION BY T.ID, S.[date], S.[time1], S.[time2]  ORDER BY S.[Lesson] ) AS rn2 	
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 TL.[Lesson] ORDER BY TL.[Priority] ) AS rn FROM #Teachers AS TL)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
WHERE
       1=1
       --AND CASE WHEN S.rn  % TC.noTeachers = 0 THEN TC.noTeachers ELSE S.RN % TC.noTeachers  END =T.rn
       --AND 
)
SELECT 
      S.[ID], S.[Lesson], S.[date], S.[time1], S.[time2], S.ID, S.Name	
FROM
     cteFinal AS S
     OUTER APPLY
     (SELECT COUNT(1) AS dup
        WHERE EXISTS(SELECT * FROM cteFinal AS F 
                   WHERE S.Lesson = F.Lesson
                        AND F.rn2>1)
     )OA
WHERE
     1=1
     --AND rn2 = 1
     
     AND CASE WHEN Srn  % noTeachers = 0 AND OA.dup = 0 THEN noTeachers 
              WHEN Srn  % noTeachers <> 0 AND OA.dup = 0  THEN Srn % noTeachers 
              WHEN Srn  % noTeachers = 0 AND OA.dup <> 0  THEN OA.dup
              WHEN Srn  % noTeachers <> 0  AND OA.dup <> 0  THEN Srn % noTeachers  + OA.dup
              ELSE Srn % noTeachers  
          END = Trn
       
     
ORDER BY
    S.[Lesson] , S.[date], S.[time1], S.[time2] ,rn
16 
ID	Lesson	date	time1	time2	ID	Name
004	Design	08/01/2017 00:00:00	13:00:00	15:00:00	004	Noah
001	Design	08/01/2017 00:00:00	17:00:00	19:00:00	001	John
005	Design	08/02/2017 00:00:00	17:00:00	19:00:00	005	Alex
002	Math	08/01/2017 00:00:00	17:00:00	19:00:00	002	Mike
001	Math	08/02/2017 00:00:00	13:00:00	15:00:00	001	John
002	Math	08/03/2017 00:00:00	17:00:00	19:00:00	002	Mike
001	Math	08/04/2017 00:00:00	13:00:00	15:00:00	001	John
17

Great..thanks a lot..