Start of School Year Process

Annual process for extending clinician, student, and calendar records into the new school year. This is a critical operational task that must be run before each new school year begins.

Update the Script Each Year

The school year variables must be updated each year before running. The Calendar table uses the YYYY of the end date (e.g., ‘2026’ = 2025-2026 school year).

Overview

The start-of-school-year script creates new records for the upcoming school year by copying records from the current school year and adjusting the date ranges. It covers three categories:

1. Clinician records — campus enrollment and qualifications
2. Student records — campus enrollment, Medicaid dates, TMHP info, and services
3. Calendar records — school calendar and exception days

Script Parameters

-- The Upcoming school Year
DECLARE @newSchoolYear VARCHAR(MAX) = '2026'
-- The Current School Year
DECLARE @CurrentSchoolYear VARCHAR(MAX) = '2025'

Clinician Extensions

Table	Purpose	Logic
ClinicianCampusEnrollment	New campus enrollment record for next school year	Finds records ending on 07/31 of current year, copies with new date range if none exists
ClinicianQualification	New qualification record for next school year	Finds records ending on 07/31 of current year, copies with new date range if none exists

Student Extensions

Table	Purpose	Logic
StudentCampusEnrollment	New campus enrollment for next school year	Copies records ending 07/31 of current year with new date range
StudentMedicaidEffectiveDate	New Medicaid effective date for next school year	Copies records ending 07/31 of current year with new date range
StudentTmhpInfo	Extends existing TMHP Info end date	Updates (not copies) the end date to end of next school year
StudentService	New service record for next school year	Copies records ending 07/31 of current year with new date range

Note

StudentTmhpInfo is the only table that updates existing records rather than creating new ones.

Calendar Extensions

Table	Purpose	Logic
Calendar	New calendar for the school year after next	Creates records 1 year beyond @newSchoolYear for ARD/cross-year overlap
CalendarExceptionDay	New exception days for the school year after next	Copies exception day records 1 year beyond @newSchoolYear

Tip

Calendar records are created for the year after the new school year to support ARDs and other records that span school years.

Verification

After running the script, verify record counts by school year:

DECLARE @Year2026 INT = 2026;
DECLARE @Year2027 INT = 2027;

-- Get date ranges from Calendar
DECLARE @Start2026 DATE = (SELECT MIN(StartDate) FROM Calendar WHERE SchoolYear = @Year2026 AND organizationid = 1)
DECLARE @End2026 DATE = (SELECT MAX(EndDate) FROM Calendar WHERE SchoolYear = @Year2026 AND organizationid = 1)
DECLARE @Start2027 DATE = (SELECT MIN(StartDate) FROM Calendar WHERE SchoolYear = @Year2027 AND organizationid = 1)
DECLARE @End2027 DATE = (SELECT MAX(EndDate) FROM Calendar WHERE SchoolYear = @Year2027 AND organizationid = 1)

SELECT SourceTable, SchoolYear, RecordCount
FROM (
    SELECT 'ClinicianCampusEnrollment' AS SourceTable, '2026' AS SchoolYear, COUNT(*) AS RecordCount
    FROM ClinicianCampusEnrollment
    WHERE EndDate BETWEEN @Start2026 AND @End2026
    HAVING COUNT(*) > 0
    UNION ALL
    SELECT 'ClinicianQualification', '2026', COUNT(*)
    FROM ClinicianQualification
    WHERE EndDate BETWEEN @Start2026 AND @End2026
    HAVING COUNT(*) > 0
    UNION ALL
    SELECT 'StudentCampusEnrollment', '2026', COUNT(*)
    FROM StudentCampusEnrollment
    WHERE EndDate BETWEEN @Start2026 AND @End2026
    HAVING COUNT(*) > 0
    UNION ALL
    SELECT 'StudentService', '2026', COUNT(*)
    FROM StudentService
    WHERE EndDate BETWEEN @Start2026 AND @End2026
    HAVING COUNT(*) > 0
    UNION ALL
    SELECT 'StudentMedicaidEffectiveDate', '2026', COUNT(*)
    FROM StudentMedicaidEffectiveDate
    WHERE EndDate BETWEEN @Start2026 AND @End2026
    HAVING COUNT(*) > 0
    UNION ALL
    SELECT 'Calendar', '2027', COUNT(*)
    FROM Calendar
    WHERE EndDate BETWEEN @Start2027 AND @End2027
    HAVING COUNT(*) > 0
) AS Results
ORDER BY SourceTable, SchoolYear;

Each table should show non-zero counts for the new school year after the script runs successfully.