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 not yet rolled over into the new school year and that have an SMS table already decently well developed but you wish to switch over to using Flex Scheduling next school year.

Scripts to convert your scheduling environment to Flex Scheduling after you have rolled over to the new school year, but before you have rolled from SMS to MST are further down this page.

Parameters to Adjust Before Running:

@SC - This is the Aeries School Code to be processed
@NextSchoolYear - This is the 9-digit school year for next year: '2023-2024'
@SampleMon - This is the Monday of an Example Week during next school year: '9/6/2023'
@SampleTue - This is the Tuesday of an Example Week during next school year: '9/7/2023'
@SampleWed - This is the Wednesday of an Example Week during next school year: '9/8/2023'
@SampleThu - This is the Thursday of an Example Week during next school year: '9/9/2023'
@SampleFri - This is the Friday of an Example Week during next school year: '9/10/2023'

IN 2022-2023 SCHOOL YEAR

Create Flex Periods based on current BEL table and assign them to any existing SMS records. This is a standard starting point to work from.

-- Create Flex Periods based on current BEL table and assign them to any existing SMS records.  This is a standard starting point to work from.
 
DECLARE @SC int
DECLARE @NextSchoolYear varchar(9)
SET @SC = 994
SET @NextSchoolYear = '2022-2023'
 
INSERT INTO FTF (SC, YR, STI, DE, ST, ET)
  SELECT SC, @NextSchoolYear, 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 (@NextSchoolYear + '_' + CONVERT(varchar, @SC) + '_' + CONVERT(varchar, PD)) NOT IN (SELECT (YR + '_' + CONVERT(varchar, SC) + '_' + STI) FROM FTF WHERE SC = @SC)
 
UPDATE SMS SET SMS.FSQ = FTF.SQ
  FROM SMS INNER JOIN FTF ON SMS.SC = FTF.SC AND CONVERT(varchar, SMS.PD) = FTF.STI
  WHERE SMS.DEL = 0 AND SMS.SC = @SC AND FTF.SC = @SC AND SMS.FSQ = 0 AND FTF.YR = @NextSchoolYear

IN 2022-2023 SCHOOL YEAR

Create Class Calendar Records according to the data in SMS and associate the SMS records

--Create Class Calendar Records according to the data in SMS and associate the SMS records
 
DECLARE @SQ int
DECLARE @SC int
DECLARE @NextSchoolYear varchar(9)
DECLARE @SampleMon Date
DECLARE @SampleTue Date
DECLARE @SampleWed Date
DECLARE @SampleThu Date
DECLARE @SampleFri Date
 
SET @SC = 994
SET @NextSchoolYear = '2023-2024'
SET @SampleMon = '9/6/2023'
SET @SampleTue = '9/7/2023'
SET @SampleWed = '9/8/2023'
SET @SampleThu = '9/9/2023'
SET @SampleFri = '9/10/2023'
 
IF NOT EXISTS(SELECT SQ FROM CCL WHERE DEL = 0 AND SC = @SC AND STI = 'MTWRF' AND YR = @NextSchoolYear)
BEGIN
    INSERT INTO CCL (SC, YR, STI) VALUES (@SC, @NextSchoolYear, 'MTWRF')
    SET @SQ = @@IDENTITY
 
    INSERT INTO CCD (CSQ, DT) VALUES (@SQ, @SampleMon)
    INSERT INTO CCD (CSQ, DT) VALUES (@SQ, @SampleTue)
    INSERT INTO CCD (CSQ, DT) VALUES (@SQ, @SampleWed)
    INSERT INTO CCD (CSQ, DT) VALUES (@SQ, @SampleThu)
    INSERT INTO CCD (CSQ, DT) VALUES (@SQ, @SampleFri)
 
    UPDATE SMS SET CSQ = @SQ WHERE SC = @SC AND DEL = 0
END
 
IF EXISTS(SELECT DY FROM SMS 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 = @NextSchoolYear)
    BEGIN
        INSERT INTO CCL (SC, YR, STI) VALUES (@SC, @NextSchoolYear, 'A-DAYS')
        SET @SQ = @@IDENTITY
 
        INSERT INTO CCD (CSQ, DT) VALUES (@SQ, @SampleMon)
        INSERT INTO CCD (CSQ, DT) VALUES (@SQ, @SampleWed)
 
        UPDATE SMS SET CSQ = @SQ WHERE SC = @SC AND DEL = 0 AND DY = 'A-DAYS'
    END
END
 
IF EXISTS(SELECT DY FROM SMS 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 = @NextSchoolYear)
    BEGIN
        INSERT INTO CCL (SC, YR, STI) VALUES (@SC, @NextSchoolYear, 'B-DAYS')
        SET @SQ = @@IDENTITY
 
        INSERT INTO CCD (CSQ, DT) VALUES (@SQ, @SampleTue)
        INSERT INTO CCD (CSQ, DT) VALUES (@SQ, @SampleThu)
 
        UPDATE SMS 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 sms_cursor CURSOR FOR
    SELECT DISTINCT MM, MT, MW, MR, MF
    FROM SMS
    WHERE SC = @SC AND DEL = 0 AND DY NOT IN ('MTWRF','MTWTF','A-DAYS','B-DAYS');
 
OPEN sms_cursor
FETCH NEXT FROM sms_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 = @NextSchoolYear)
    BEGIN
        INSERT INTO CCL (SC, YR, STI) VALUES (@SC, @NextSchoolYear, @PATTERN)
        SET @SQ = @@IDENTITY
 
        IF @M = 1
            INSERT INTO CCD (CSQ, DT) VALUES (@SQ, @SampleMon)
        IF @T = 1
            INSERT INTO CCD (CSQ, DT) VALUES (@SQ, @SampleTue)
        IF @W = 1
            INSERT INTO CCD (CSQ, DT) VALUES (@SQ, @SampleWed)
        IF @R = 1
            INSERT INTO CCD (CSQ, DT) VALUES (@SQ, @SampleThu)
        IF @F = 1
            INSERT INTO CCD (CSQ, DT) VALUES (@SQ, @SampleFri)
 
        UPDATE SMS 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 sms_cursor INTO @M, @T, @W, @R, @F
END
CLOSE sms_cursor;
DEALLOCATE sms_cursor;

IN 2022-2023 SCHOOL YEAR

Create Section Staff (SSM) Records linked to SMS

-- Create Section Staff (SSM) Records linked to SMS
 
DECLARE @SC int
SET @SC = 994
 
-- SMS.TN & TCH.ID
INSERT INTO SSM (SC, SE, ID, PR)
  SELECT SMS.SC, SMS.SE, TCH.ID, 1 PrimaryTchr
  FROM SMS INNER JOIN TCH ON SMS.SC = TCH.SC AND SMS.TN = TCH.TN LEFT JOIN SSM ON SMS.SC = SSM.SC AND SMS.SE = SSM.SE AND SSM.ID = TCH.ID
  WHERE SMS.DEL = 0 AND SMS.SC = @SC AND TCH.TN > 0 AND TCH.ID > 0 AND SSM.ID IS NULL
 
-- SMS.TN & TCH.ID2
INSERT INTO SSM (SC, SE, ID, PR)
  SELECT SMS.SC, SMS.SE, TCH.ID2, 0 PrimaryTchr
  FROM SMS INNER JOIN TCH ON SMS.SC = TCH.SC AND SMS.TN = TCH.TN LEFT JOIN SSM ON SMS.SC = SSM.SC AND SMS.SE = SSM.SE AND SSM.ID = TCH.ID2
  WHERE SMS.DEL = 0 AND SMS.SC = @SC AND TCH.TN > 0 AND TCH.ID2 > 0 AND SSM.ID IS NULL
 
-- SMS.TN & TCH.ID3
INSERT INTO SSM (SC, SE, ID, PR)
  SELECT SMS.SC, SMS.SE, TCH.ID3, 0 PrimaryTchr
  FROM SMS INNER JOIN TCH ON SMS.SC = TCH.SC AND SMS.TN = TCH.TN LEFT JOIN SSM ON SMS.SC = SSM.SC AND SMS.SE = SSM.SE AND SSM.ID = TCH.ID3
  WHERE SMS.DEL = 0 AND SMS.SC = @SC AND TCH.TN > 0 AND TCH.ID3 > 0 AND SSM.ID IS NULL
 
-- SMS.TN2 & TCH.ID
INSERT INTO SSM (SC, SE, ID, PR)
  SELECT SMS.SC, SMS.SE, TCH.ID, 0 PrimaryTchr
  FROM SMS INNER JOIN TCH ON SMS.SC = TCH.SC AND SMS.TN2 = TCH.TN LEFT JOIN SSM ON SMS.SC = SSM.SC AND SMS.SE = SSM.SE AND SSM.ID = TCH.ID
  WHERE SMS.DEL = 0 AND SMS.SC = @SC AND TCH.TN > 0 AND TCH.ID > 0 AND SSM.ID IS NULL
 
-- SMS.TN2 & TCH.ID2
INSERT INTO SSM (SC, SE, ID, PR)
  SELECT SMS.SC, SMS.SE, TCH.ID2, 0 PrimaryTchr
  FROM SMS INNER JOIN TCH ON SMS.SC = TCH.SC AND SMS.TN2 = TCH.TN LEFT JOIN SSM ON SMS.SC = SSM.SC AND SMS.SE = SSM.SE AND SSM.ID = TCH.ID2
  WHERE SMS.DEL = 0 AND SMS.SC = @SC AND TCH.TN > 0 AND TCH.ID2 > 0 AND SSM.ID IS NULL
 
-- SMS.TN2 & TCH.ID3
INSERT INTO SSM (SC, SE, ID, PR)
  SELECT SMS.SC, SMS.SE, TCH.ID3, 0 PrimaryTchr
  FROM SMS INNER JOIN TCH ON SMS.SC = TCH.SC AND SMS.TN2 = TCH.TN LEFT JOIN SSM ON SMS.SC = SSM.SC AND SMS.SE = SSM.SE AND SSM.ID = TCH.ID3
  WHERE SMS.DEL = 0 AND SMS.SC = @SC AND TCH.TN > 0 AND TCH.ID3 > 0 AND SSM.ID IS NULL
 
-- SMS.TN3 & TCH.ID
INSERT INTO SSM (SC, SE, ID, PR)
  SELECT SMS.SC, SMS.SE, TCH.ID, 0 PrimaryTchr
  FROM SMS INNER JOIN TCH ON SMS.SC = TCH.SC AND SMS.TN3 = TCH.TN LEFT JOIN SSM ON SMS.SC = SSM.SC AND SMS.SE = SSM.SE AND SSM.ID = TCH.ID
  WHERE SMS.DEL = 0 AND SMS.SC = @SC AND TCH.TN > 0 AND TCH.ID > 0 AND SSM.ID IS NULL
 
-- SMS.TN3 & TCH.ID2
INSERT INTO SSM (SC, SE, ID, PR)
  SELECT SMS.SC, SMS.SE, TCH.ID2, 0 PrimaryTchr
  FROM SMS INNER JOIN TCH ON SMS.SC = TCH.SC AND SMS.TN3 = TCH.TN LEFT JOIN SSM ON SMS.SC = SSM.SC AND SMS.SE = SSM.SE AND SSM.ID = TCH.ID2
  WHERE SMS.DEL = 0 AND SMS.SC = @SC AND TCH.TN > 0 AND TCH.ID2 > 0 AND SSM.ID IS NULL
 
-- SMS.TN3 & TCH.ID3
INSERT INTO SSM (SC, SE, ID, PR)
  SELECT SMS.SC, SMS.SE, TCH.ID3, 0 PrimaryTchr
  FROM SMS INNER JOIN TCH ON SMS.SC = TCH.SC AND SMS.TN3 = TCH.TN LEFT JOIN SSM ON SMS.SC = SSM.SC AND SMS.SE = SSM.SE AND SSM.ID = TCH.ID3
  WHERE SMS.DEL = 0 AND SMS.SC = @SC AND TCH.TN > 0 AND TCH.ID3 > 0 AND SSM.ID IS NULL

IN 2022-2023 SCHOOL YEAR

Clear out SMS table of Old Scheduling Fields

--Clear out SMS table of Old Scheduling Fields
  
DECLARE @SC int
SET @SC = 994
  
UPDATE SMS 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)

IN 2022-2023 SCHOOL YEAR

Some Useful Queries

DECLARE @SC int
SET @SC = 994
 
-- The following lists sections that do not have a Flex Period Assigned:
SELECT SMS.SC, SMS.SE, SMS.PD, SMS.FSQ FROM SMS WHERE FSQ = 0 AND SC = @SC
 
-- List Flex Periods in a school:
SELECT * FROM FTF WHERE SC = @SC
 
-- List all Section Staff
SELECT * FROM SSM
 
-- List Sections without Section Staff
SELECT SMS.* FROM SMS LEFT JOIN SSM ON SMS.SC = SSM.SC AND SMS.SE = SSM.SE AND SSM.DEL = 0
WHERE SMS.DEL = 0 AND SMS.SC = @SC AND SSM.SE IS NULL
 
-- List Sections without a Primary Teacher
SELECT SMS.* FROM SMS LEFT JOIN SSM ON SMS.SC = SSM.SC AND SMS.SE = SSM.SE AND SSM.DEL = 0 AND SSM.PR = 1
WHERE SMS.DEL = 0 AND SMS.SC = @SC AND SSM.SE IS NULL

IN 2023-2024 SCHOOL YEAR

(but you have not yet rolled from SMS to MST)

Create Flex Periods based on current BEL table and assign them to any existing SMS records. This is a standard starting point to work from.

-- Create Flex Periods based on current BEL table and assign them to any existing SMS 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, T1, T2
  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 SMS SET SMS.FSQ = FTF.SQ
  FROM SMS INNER JOIN FTF ON SMS.SC = FTF.SC AND CONVERT(varchar, SMS.PD) = FTF.STI
  WHERE SMS.DEL = 0 AND SMS.SC = @SC AND FTF.SC = @SC AND SMS.FSQ = 0 AND FTF.YR = @CurrentSchoolYear

IN 2023-2024 SCHOOL YEAR

Create Class Calendar Records according to the data in SMS and associate the SMS records

--Create Class Calendar Records according to the data in SMS and DAY and associate the SMS records
 
DECLARE @SQ int
DECLARE @SC int
DECLARE @CurrentSchoolYear varchar(9)
DECLARE @SampleMon Date
DECLARE @SampleTue Date
DECLARE @SampleWed Date
DECLARE @SampleThu Date
DECLARE @SampleFri Date
 
SET @SC = 994
SET @CurrentSchoolYear = '2023-2024'
SET @SampleMon = '9/6/2023'
SET @SampleTue = '9/7/2023'
SET @SampleWed = '9/8/2023'
SET @SampleThu = '9/9/2023'
SET @SampleFri = '9/10/2023'
 
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
 
    IF EXISTS(SELECT TOP 1 DY FROM [DAY] WHERE SC = @SC AND DEL = 0)
    BEGIN
        INSERT INTO CCD (CSQ, DT) SELECT @SQ, DT FROM [DAY] WHERE DEL = 0 AND SC = @SC AND (NOT HO > ' ' OR HO = '%' OR HO = '+')
    END
    ELSE
    BEGIN
        INSERT INTO CCD (CSQ, DT) VALUES (@SQ, @SampleMon)
        INSERT INTO CCD (CSQ, DT) VALUES (@SQ, @SampleTue)
        INSERT INTO CCD (CSQ, DT) VALUES (@SQ, @SampleWed)
        INSERT INTO CCD (CSQ, DT) VALUES (@SQ, @SampleThu)
        INSERT INTO CCD (CSQ, DT) VALUES (@SQ, @SampleFri)
    END
 
    UPDATE SMS SET CSQ = @SQ WHERE SC = @SC AND DEL = 0
END
 
IF EXISTS(SELECT DY FROM SMS 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
 
        IF EXISTS(SELECT TOP 1 DY FROM [DAY] WHERE SC = @SC AND DEL = 0)
        BEGIN
            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'
        END
        ELSE
        BEGIN
            INSERT INTO CCD (CSQ, DT) VALUES (@SQ, @SampleMon)
            INSERT INTO CCD (CSQ, DT) VALUES (@SQ, @SampleWed)
        END
 
        UPDATE SMS SET CSQ = @SQ WHERE SC = @SC AND DEL = 0 AND DY = 'A-DAYS'
    END
END
 
IF EXISTS(SELECT DY FROM SMS 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
 
        IF EXISTS(SELECT TOP 1 DY FROM [DAY] WHERE SC = @SC AND DEL = 0)
        BEGIN
            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'
        END
        ELSE
        BEGIN
            INSERT INTO CCD (CSQ, DT) VALUES (@SQ, @SampleTue)
            INSERT INTO CCD (CSQ, DT) VALUES (@SQ, @SampleThu)
        END
 
        UPDATE SMS 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 sms_cursor CURSOR FOR
    SELECT DISTINCT MM, MT, MW, MR, MF
    FROM SMS
    WHERE SC = @SC AND DEL = 0 AND DY NOT IN ('MTWRF','MTWTF','A-DAYS','B-DAYS');
 
OPEN sms_cursor
FETCH NEXT FROM sms_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 EXISTS(SELECT TOP 1 DY FROM [DAY] WHERE SC = @SC AND DEL = 0)
        BEGIN
            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
        END
        ELSE
        BEGIN
            IF @M = 1
                INSERT INTO CCD (CSQ, DT) VALUES (@SQ, @SampleMon)
            IF @T = 1
                INSERT INTO CCD (CSQ, DT) VALUES (@SQ, @SampleTue)
            IF @W = 1
                INSERT INTO CCD (CSQ, DT) VALUES (@SQ, @SampleWed)
            IF @R = 1
                INSERT INTO CCD (CSQ, DT) VALUES (@SQ, @SampleThu)
            IF @F = 1
                INSERT INTO CCD (CSQ, DT) VALUES (@SQ, @SampleFri)
        END
 
        UPDATE SMS 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 sms_cursor INTO @M, @T, @W, @R, @F
END
CLOSE sms_cursor;
DEALLOCATE sms_cursor;

IN 2023-2024 SCHOOL YEAR

Create Section Staff (SSM) Records linked to SMS

-- Create Section Staff (SSM) Records linked to SMS
 
DECLARE @SC int
SET @SC = 994
 
-- SMS.TN & TCH.ID
INSERT INTO SSM (SC, SE, ID, PR)
  SELECT SMS.SC, SMS.SE, TCH.ID, 1 PrimaryTchr
  FROM SMS INNER JOIN TCH ON SMS.SC = TCH.SC AND SMS.TN = TCH.TN LEFT JOIN SSM ON SMS.SC = SSM.SC AND SMS.SE = SSM.SE AND SSM.ID = TCH.ID
  WHERE SMS.DEL = 0 AND SMS.SC = @SC AND TCH.TN > 0 AND TCH.ID > 0 AND SSM.ID IS NULL
 
-- SMS.TN & TCH.ID2
INSERT INTO SSM (SC, SE, ID, PR)
  SELECT SMS.SC, SMS.SE, TCH.ID2, 0 PrimaryTchr
  FROM SMS INNER JOIN TCH ON SMS.SC = TCH.SC AND SMS.TN = TCH.TN LEFT JOIN SSM ON SMS.SC = SSM.SC AND SMS.SE = SSM.SE AND SSM.ID = TCH.ID2
  WHERE SMS.DEL = 0 AND SMS.SC = @SC AND TCH.TN > 0 AND TCH.ID2 > 0 AND SSM.ID IS NULL
 
-- SMS.TN & TCH.ID3
INSERT INTO SSM (SC, SE, ID, PR)
  SELECT SMS.SC, SMS.SE, TCH.ID3, 0 PrimaryTchr
  FROM SMS INNER JOIN TCH ON SMS.SC = TCH.SC AND SMS.TN = TCH.TN LEFT JOIN SSM ON SMS.SC = SSM.SC AND SMS.SE = SSM.SE AND SSM.ID = TCH.ID3
  WHERE SMS.DEL = 0 AND SMS.SC = @SC AND TCH.TN > 0 AND TCH.ID3 > 0 AND SSM.ID IS NULL
 
-- SMS.TN2 & TCH.ID
INSERT INTO SSM (SC, SE, ID, PR)
  SELECT SMS.SC, SMS.SE, TCH.ID, 0 PrimaryTchr
  FROM SMS INNER JOIN TCH ON SMS.SC = TCH.SC AND SMS.TN2 = TCH.TN LEFT JOIN SSM ON SMS.SC = SSM.SC AND SMS.SE = SSM.SE AND SSM.ID = TCH.ID
  WHERE SMS.DEL = 0 AND SMS.SC = @SC AND TCH.TN > 0 AND TCH.ID > 0 AND SSM.ID IS NULL
 
-- SMS.TN2 & TCH.ID2
INSERT INTO SSM (SC, SE, ID, PR)
  SELECT SMS.SC, SMS.SE, TCH.ID2, 0 PrimaryTchr
  FROM SMS INNER JOIN TCH ON SMS.SC = TCH.SC AND SMS.TN2 = TCH.TN LEFT JOIN SSM ON SMS.SC = SSM.SC AND SMS.SE = SSM.SE AND SSM.ID = TCH.ID2
  WHERE SMS.DEL = 0 AND SMS.SC = @SC AND TCH.TN > 0 AND TCH.ID2 > 0 AND SSM.ID IS NULL
 
-- SMS.TN2 & TCH.ID3
INSERT INTO SSM (SC, SE, ID, PR)
  SELECT SMS.SC, SMS.SE, TCH.ID3, 0 PrimaryTchr
  FROM SMS INNER JOIN TCH ON SMS.SC = TCH.SC AND SMS.TN2 = TCH.TN LEFT JOIN SSM ON SMS.SC = SSM.SC AND SMS.SE = SSM.SE AND SSM.ID = TCH.ID3
  WHERE SMS.DEL = 0 AND SMS.SC = @SC AND TCH.TN > 0 AND TCH.ID3 > 0 AND SSM.ID IS NULL
 
-- SMS.TN3 & TCH.ID
INSERT INTO SSM (SC, SE, ID, PR)
  SELECT SMS.SC, SMS.SE, TCH.ID, 0 PrimaryTchr
  FROM SMS INNER JOIN TCH ON SMS.SC = TCH.SC AND SMS.TN3 = TCH.TN LEFT JOIN SSM ON SMS.SC = SSM.SC AND SMS.SE = SSM.SE AND SSM.ID = TCH.ID
  WHERE SMS.DEL = 0 AND SMS.SC = @SC AND TCH.TN > 0 AND TCH.ID > 0 AND SSM.ID IS NULL
 
-- SMS.TN3 & TCH.ID2
INSERT INTO SSM (SC, SE, ID, PR)
  SELECT SMS.SC, SMS.SE, TCH.ID2, 0 PrimaryTchr
  FROM SMS INNER JOIN TCH ON SMS.SC = TCH.SC AND SMS.TN3 = TCH.TN LEFT JOIN SSM ON SMS.SC = SSM.SC AND SMS.SE = SSM.SE AND SSM.ID = TCH.ID2
  WHERE SMS.DEL = 0 AND SMS.SC = @SC AND TCH.TN > 0 AND TCH.ID2 > 0 AND SSM.ID IS NULL
 
-- SMS.TN3 & TCH.ID3
INSERT INTO SSM (SC, SE, ID, PR)
  SELECT SMS.SC, SMS.SE, TCH.ID3, 0 PrimaryTchr
  FROM SMS INNER JOIN TCH ON SMS.SC = TCH.SC AND SMS.TN3 = TCH.TN LEFT JOIN SSM ON SMS.SC = SSM.SC AND SMS.SE = SSM.SE AND SSM.ID = TCH.ID3
  WHERE SMS.DEL = 0 AND SMS.SC = @SC AND TCH.TN > 0 AND TCH.ID3 > 0 AND SSM.ID IS NULL

IN 2023-2024 SCHOOL YEAR

Clear out SMS table of Old Scheduling Fields

--Clear out SMS table of Old Scheduling Fields
  
DECLARE @SC int
SET @SC = 994
  
UPDATE SMS 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)

IN 2023-2024 SCHOOL YEAR

Some Useful Queries

DECLARE @SC int
SET @SC = 994
 
-- The following lists sections that do not have a Flex Period Assigned:
SELECT SMS.SC, SMS.SE, SMS.PD, SMS.FSQ FROM SMS WHERE FSQ = 0 AND SC = @SC
 
-- List Flex Periods in a school:
SELECT * FROM FTF WHERE SC = @SC
 
-- List all Section Staff
SELECT * FROM SSM
 
-- List Sections without Section Staff
SELECT SMS.* FROM SMS LEFT JOIN SSM ON SMS.SC = SSM.SC AND SMS.SE = SSM.SE AND SSM.DEL = 0
WHERE SMS.DEL = 0 AND SMS.SC = @SC AND SSM.SE IS NULL
 
-- List Sections without a Primary Teacher
SELECT SMS.* FROM SMS LEFT JOIN SSM ON SMS.SC = SSM.SC AND SMS.SE = SSM.SE AND SSM.DEL = 0 AND SSM.PR = 1
WHERE SMS.DEL = 0 AND SMS.SC = @SC AND SSM.SE IS NULL

Support

Flex Scheduling - Scheduling Master Schedule Initialization Scripts Print

IN 2023-2024 SCHOOL YEAR

Flex Scheduling - Scheduling Master Schedule Initialization Scripts Print

IN 2023-2024 SCHOOL YEAR

Related Articles