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: '2020-2021'


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 int
DECLARE @CurrentSchoolYear varchar(9)
SET @SC = 994
SET @CurrentSchoolYear = '2020-2021'

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 int
DECLARE @SC int
DECLARE @CurrentSchoolYear varchar(9)
SET @SC = 994
SET @CurrentSchoolYear = '2020-2021'

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 = 0
END

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'
END
END

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'
END
END

DECLARE @M bit
DECLARE @T bit
DECLARE @W bit
DECLARE @R bit
DECLARE @F bit
DECLARE @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_cursor
FETCH NEXT FROM mst_cursor INTO @M, @T, @W, @R, @F

WHILE @@FETCH_STATUS = 0
BEGIN
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, @F
END
CLOSE mst_cursor;
DEALLOCATE mst_cursor;



Create Section Staff (SSE) Records linked to MST

-- Create Section Staff (SSE) Records linked to MST

DECLARE @SC int
SET @SC = 994

-- MST.TN & TCH.ID
INSERT 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.ID2
INSERT 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.ID3
INSERT 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.ID
INSERT 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.ID2
INSERT 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.ID3
INSERT 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.ID
INSERT 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.ID2
INSERT 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.ID3
INSERT 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

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 int
SET @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 Staff
SELECT * FROM SSE

-- List Sections without Section Staff
SELECT MST.* FROM MST LEFT JOIN SSE ON MST.SC = SSE.SC AND MST.SE = SSE.SE AND SSE.DEL = 0
WHERE MST.DEL = 0 AND MST.SC = @SC AND SSE.SE IS NULL

-- List Sections without a Primary Teacher
SELECT MST.* FROM MST LEFT JOIN SSE ON MST.SC = SSE.SC AND MST.SE = SSE.SE AND SSE.DEL = 0 AND SSE.PR = 1
WHERE MST.DEL = 0 AND MST.SC = @SC AND SSE.SE IS NULL