WARNING - ONLY RUN THESE SCRIPTS IF YOU HAVE NOT YET BEGUN MANUALLY SETTING UP FLEX SCHEDULING TABLES.
BE SURE TO TEST THESE SCRIPTS IN A "TEST" DATABASE.
These scripts are designed to be used for schools that have rolled over to the new year and migrated from SMS to MST but SCHOOL HAS NOT STARTED YET. Do not run these scripts if your school year has already started. There is no method to switch to Flex Scheduling mid-year.
Parameters to Adjust Before Running:
- @SC - This is the Aeries School Code to be processed
- @CurrentSchoolYear - This is the 9-digit school year for the current year: '2023-2024'
Create Flex Periods based on current BEL table and assign them to any existing MST records. This is a standard starting point to work from.
-- Create Flex Periods based on current BEL table and assign them to any existing MST records. This is a standard starting point to work from.DECLARE @SC intDECLARE @CurrentSchoolYear varchar(9)SET @SC = 994SET @CurrentSchoolYear = '2023-2024'INSERT INTO FTF (SC, YR, STI, DE, ST, ET) SELECT SC, @CurrentSchoolYear, CONVERT(varchar, PD) STI, ('Pd ' + CONVERT(varchar, PD)) DE , '1/1/1900 ' + CONVERT(varchar, BEL.T1, 114) , '1/1/1900 ' + CONVERT(varchar, BEL.T2, 114) FROM BEL WHERE SC = @SC AND DEL = 0 AND DY = 0 AND (@CurrentSchoolYear + '_' + CONVERT(varchar, @SC) + '_' + CONVERT(varchar, PD)) NOT IN (SELECT (YR + '_' + CONVERT(varchar, SC) + '_' + STI) FROM FTF WHERE SC = @SC) UPDATE MST SET MST.FSQ = FTF.SQ FROM MST INNER JOIN FTF ON MST.SC = FTF.SC AND CONVERT(varchar, MST.PD) = FTF.STI WHERE MST.DEL = 0 AND MST.SC = @SC AND FTF.SC = @SC AND MST.FSQ = 0 AND FTF.YR = @CurrentSchoolYear
Create Class Calendar Records according to the data in MST and DAY and associate the MST records
-- Create current school year Class Calendar records based on MST Day of Week values and the DAY table.DECLARE @SQ intDECLARE @SC intDECLARE @CurrentSchoolYear varchar(9)SET @SC = 994SET @CurrentSchoolYear = '2023-2024'IF NOT EXISTS(SELECT SQ FROM CCL WHERE DEL = 0 AND SC = @SC AND STI = 'MTWRF' AND YR = @CurrentSchoolYear)BEGIN INSERT INTO CCL (SC, YR, STI) VALUES (@SC, @CurrentSchoolYear, 'MTWRF') SET @SQ = @@IDENTITY INSERT INTO CCD (CSQ, DT) SELECT @SQ, DT FROM [DAY] WHERE DEL = 0 AND SC = @SC AND (NOT HO > ' ' OR HO = '%' OR HO = '+') UPDATE MST SET CSQ = @SQ WHERE SC = @SC AND DEL = 0 AND CSQ = 0END IF EXISTS(SELECT DY FROM MST WHERE SC = @SC AND DY = 'A-DAYS')BEGIN IF NOT EXISTS(SELECT SQ FROM CCL WHERE DEL = 0 AND SC = @SC AND STI = 'A-DAYS' AND YR = @CurrentSchoolYear) BEGIN INSERT INTO CCL (SC, YR, STI) VALUES (@SC, @CurrentSchoolYear, 'A-DAYS') SET @SQ = @@IDENTITY INSERT INTO CCD (CSQ, DT) SELECT @SQ, DT FROM [DAY] WHERE DEL = 0 AND SC = @SC AND (NOT HO > ' ' OR HO = '%' OR HO = '+') AND AB = 'A' UPDATE MST SET CSQ = @SQ WHERE SC = @SC AND DEL = 0 AND DY = 'A-DAYS' ENDEND IF EXISTS(SELECT DY FROM MST WHERE SC = @SC AND DY = 'B-DAYS')BEGIN IF NOT EXISTS(SELECT SQ FROM CCL WHERE DEL = 0 AND SC = @SC AND STI = 'B-DAYS' AND YR = @CurrentSchoolYear) BEGIN INSERT INTO CCL (SC, YR, STI) VALUES (@SC, @CurrentSchoolYear, 'B-DAYS') SET @SQ = @@IDENTITY INSERT INTO CCD (CSQ, DT) SELECT @SQ, DT FROM [DAY] WHERE DEL = 0 AND SC = @SC AND (NOT HO > ' ' OR HO = '%' OR HO = '+') AND AB = 'B' UPDATE MST SET CSQ = @SQ WHERE SC = @SC AND DEL = 0 AND DY = 'B-DAYS' ENDEND DECLARE @M bitDECLARE @T bitDECLARE @W bitDECLARE @R bitDECLARE @F bitDECLARE @PATTERN varchar(5) DECLARE mst_cursor CURSOR FOR SELECT DISTINCT MM, MT, MW, MR, MF FROM MST WHERE SC = @SC AND DEL = 0 AND DY NOT IN ('MTWRF','MTWTF','A-DAYS','B-DAYS'); OPEN mst_cursorFETCH NEXT FROM mst_cursor INTO @M, @T, @W, @R, @F WHILE @@FETCH_STATUS = 0BEGIN SET @PATTERN = '' IF @M = 1 SET @PATTERN = @PATTERN + 'M' IF @T = 1 SET @PATTERN = @PATTERN + 'T' IF @W = 1 SET @PATTERN = @PATTERN + 'W' IF @R = 1 SET @PATTERN = @PATTERN + 'R' IF @F = 1 SET @PATTERN = @PATTERN + 'F' IF NOT EXISTS(SELECT SQ FROM CCL WHERE DEL = 0 AND SC = @SC AND STI = @PATTERN AND YR = @CurrentSchoolYear) BEGIN INSERT INTO CCL (SC, YR, STI) VALUES (@SC, @CurrentSchoolYear, @PATTERN) SET @SQ = @@IDENTITY IF @M = 1 INSERT INTO CCD (CSQ, DT) SELECT @SQ, DT FROM [DAY] WHERE DEL = 0 AND SC = @SC AND (NOT HO > ' ' OR HO = '%' OR HO = '+') AND DY % 5 = 1 IF @T = 1 INSERT INTO CCD (CSQ, DT) SELECT @SQ, DT FROM [DAY] WHERE DEL = 0 AND SC = @SC AND (NOT HO > ' ' OR HO = '%' OR HO = '+') AND DY % 5 = 2 IF @W = 1 INSERT INTO CCD (CSQ, DT) SELECT @SQ, DT FROM [DAY] WHERE DEL = 0 AND SC = @SC AND (NOT HO > ' ' OR HO = '%' OR HO = '+') AND DY % 5 = 3 IF @R = 1 INSERT INTO CCD (CSQ, DT) SELECT @SQ, DT FROM [DAY] WHERE DEL = 0 AND SC = @SC AND (NOT HO > ' ' OR HO = '%' OR HO = '+') AND DY % 5 = 4 IF @F = 1 INSERT INTO CCD (CSQ, DT) SELECT @SQ, DT FROM [DAY] WHERE DEL = 0 AND SC = @SC AND (NOT HO > ' ' OR HO = '%' OR HO = '+') AND DY % 5 = 0 UPDATE MST SET CSQ = @SQ WHERE SC = @SC AND DEL = 0 AND DY NOT IN ('MTWRF','MTWTF','A-DAYS','B-DAYS') AND MM=@M AND MT=@T AND MW=@W AND MR=@R AND MF=@F END FETCH NEXT FROM mst_cursor INTO @M, @T, @W, @R, @FENDCLOSE mst_cursor;DEALLOCATE mst_cursor;
Create Section Staff (SSE) Records linked to MST
-- Create Section Staff (SSE) Records linked to MST DECLARE @SC intSET @SC = 994 -- MST.TN & TCH.IDINSERT INTO SSE (SC, SE, ID, PR) SELECT MST.SC, MST.SE, TCH.ID, 1 PrimaryTchr FROM MST INNER JOIN TCH ON MST.SC = TCH.SC AND MST.TN = TCH.TN LEFT JOIN SSE ON MST.SC = SSE.SC AND MST.SE = SSE.SE AND SSE.ID = TCH.ID WHERE MST.DEL = 0 AND MST.SC = @SC AND TCH.TN > 0 AND TCH.ID > 0 AND SSE.ID IS NULL -- MST.TN & TCH.ID2INSERT INTO SSE (SC, SE, ID, PR) SELECT MST.SC, MST.SE, TCH.ID2, 0 PrimaryTchr FROM MST INNER JOIN TCH ON MST.SC = TCH.SC AND MST.TN = TCH.TN LEFT JOIN SSE ON MST.SC = SSE.SC AND MST.SE = SSE.SE AND SSE.ID = TCH.ID2 WHERE MST.DEL = 0 AND MST.SC = @SC AND TCH.TN > 0 AND TCH.ID2 > 0 AND SSE.ID IS NULL -- MST.TN & TCH.ID3INSERT INTO SSE (SC, SE, ID, PR) SELECT MST.SC, MST.SE, TCH.ID3, 0 PrimaryTchr FROM MST INNER JOIN TCH ON MST.SC = TCH.SC AND MST.TN = TCH.TN LEFT JOIN SSE ON MST.SC = SSE.SC AND MST.SE = SSE.SE AND SSE.ID = TCH.ID3 WHERE MST.DEL = 0 AND MST.SC = @SC AND TCH.TN > 0 AND TCH.ID3 > 0 AND SSE.ID IS NULL -- MST.TN2 & TCH.IDINSERT INTO SSE (SC, SE, ID, PR) SELECT MST.SC, MST.SE, TCH.ID, 0 PrimaryTchr FROM MST INNER JOIN TCH ON MST.SC = TCH.SC AND MST.TN2 = TCH.TN LEFT JOIN SSE ON MST.SC = SSE.SC AND MST.SE = SSE.SE AND SSE.ID = TCH.ID WHERE MST.DEL = 0 AND MST.SC = @SC AND TCH.TN > 0 AND TCH.ID > 0 AND SSE.ID IS NULL -- MST.TN2 & TCH.ID2INSERT INTO SSE (SC, SE, ID, PR) SELECT MST.SC, MST.SE, TCH.ID2, 0 PrimaryTchr FROM MST INNER JOIN TCH ON MST.SC = TCH.SC AND MST.TN2 = TCH.TN LEFT JOIN SSE ON MST.SC = SSE.SC AND MST.SE = SSE.SE AND SSE.ID = TCH.ID2 WHERE MST.DEL = 0 AND MST.SC = @SC AND TCH.TN > 0 AND TCH.ID2 > 0 AND SSE.ID IS NULL -- MST.TN2 & TCH.ID3INSERT INTO SSE (SC, SE, ID, PR) SELECT MST.SC, MST.SE, TCH.ID3, 0 PrimaryTchr FROM MST INNER JOIN TCH ON MST.SC = TCH.SC AND MST.TN2 = TCH.TN LEFT JOIN SSE ON MST.SC = SSE.SC AND MST.SE = SSE.SE AND SSE.ID = TCH.ID3 WHERE MST.DEL = 0 AND MST.SC = @SC AND TCH.TN > 0 AND TCH.ID3 > 0 AND SSE.ID IS NULL -- MST.TN3 & TCH.IDINSERT INTO SSE (SC, SE, ID, PR) SELECT MST.SC, MST.SE, TCH.ID, 0 PrimaryTchr FROM MST INNER JOIN TCH ON MST.SC = TCH.SC AND MST.TN3 = TCH.TN LEFT JOIN SSE ON MST.SC = SSE.SC AND MST.SE = SSE.SE AND SSE.ID = TCH.ID WHERE MST.DEL = 0 AND MST.SC = @SC AND TCH.TN > 0 AND TCH.ID > 0 AND SSE.ID IS NULL -- MST.TN3 & TCH.ID2INSERT INTO SSE (SC, SE, ID, PR) SELECT MST.SC, MST.SE, TCH.ID2, 0 PrimaryTchr FROM MST INNER JOIN TCH ON MST.SC = TCH.SC AND MST.TN3 = TCH.TN LEFT JOIN SSE ON MST.SC = SSE.SC AND MST.SE = SSE.SE AND SSE.ID = TCH.ID2 WHERE MST.DEL = 0 AND MST.SC = @SC AND TCH.TN > 0 AND TCH.ID2 > 0 AND SSE.ID IS NULL -- MST.TN3 & TCH.ID3INSERT INTO SSE (SC, SE, ID, PR) SELECT MST.SC, MST.SE, TCH.ID3, 0 PrimaryTchr FROM MST INNER JOIN TCH ON MST.SC = TCH.SC AND MST.TN3 = TCH.TN LEFT JOIN SSE ON MST.SC = SSE.SC AND MST.SE = SSE.SE AND SSE.ID = TCH.ID3 WHERE MST.DEL = 0 AND MST.SC = @SC AND TCH.TN > 0 AND TCH.ID3 > 0 AND SSE.ID IS NULL
Clear out MST of Old Scheduling Fields
--Clear out MST of Old Scheduling Fields
--These can be run at any time upon confirming that converting data to Flex is correct.
DECLARE @SC int
SET @SC = 994
UPDATE MST SET PD = 0, BL = 0, TN = 0, DY = '', MM = 0, MT = 0, MW = 0, MR = 0, MF = 0, MS = 0 WHERE SC = @SC AND (PD <> 0 OR BL <> 0 OR TN <> 0 OR DY <> '' OR MM <> 0 OR MT <> 0 OR MW <> 0 OR MR <> 0 OR MF <> 0 OR MS <> 0)
Some Useful Queries
DECLARE @SC intSET @SC = 994 -- The following lists sections that do not have a Flex Period Assigned:SELECT MST.SC, MST.SE, MST.PD, MST.FSQ FROM MST WHERE FSQ = 0 AND SC = @SC -- List Flex Periods in a school:SELECT * FROM FTF WHERE SC = @SC -- List all Section StaffSELECT * FROM SSE -- List Sections without Section StaffSELECT MST.* FROM MST LEFT JOIN SSE ON MST.SC = SSE.SC AND MST.SE = SSE.SE AND SSE.DEL = 0WHERE MST.DEL = 0 AND MST.SC = @SC AND SSE.SE IS NULL -- List Sections without a Primary TeacherSELECT MST.* FROM MST LEFT JOIN SSE ON MST.SC = SSE.SC AND MST.SE = SSE.SE AND SSE.DEL = 0 AND SSE.PR = 1WHERE MST.DEL = 0 AND MST.SC = @SC AND SSE.SE IS NULL