Flex Scheduling - Master Schedule Initialization Scripts : Aeries Software

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

Support

Flex Scheduling - Master Schedule Initialization Scripts Print

Flex Scheduling - Master Schedule Initialization Scripts Print

Related Articles