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