How to make changes to the Calendar (DAY) mid-year without causing errors in related tables.
- This process is utilized when dates need to be added/removed from the Calendar and Attendance has already been initialized.
- This process is used when changes need to be made to the Calendar at one site
Step 1: Backup all of the affected tables
- Example
SELECT * INTO ATT_20240101 FROM ATT
SELECT * INTO CAR_20240101 FROM CAR
SELECT * INTO DAY_20240101 FROM DAY
SELECT * INTO LEA_20240101 FROM LEA
Step 2: Make the necessary changes to the Calendar at the affected School site
- Example Site 50
Step 3: Correct corresponding tables with the new Day numbers
In each script, replace the values in parenthesis with the affected School Code
- ATT: UPDATE ATT SET ATT.DY = DAY.DY FROM ATT INNER JOIN DAY on ATT.SC = DAY.SC and ATT.DT = DAY.DT
WHERE ATT.DY <> DAY.DY and ATT.DY >= 0 and ATT.DY <= 300
AND ATT.SC in ( 50 )
- CAR start date: UPDATE CAR SET CAR.D1 = DAY.DY FROM CAR INNER JOIN DAY on CAR.SC = DAY.SC and CAR.DS = DAY.DT
WHERE CAR.D1 <> DAY.DY
AND CAR.SC in ( 50 ) - CAR end date: UPDATE CAR SET CAR.D2 = DAY.DY FROM CAR INNER JOIN DAY on CAR.SC = DAY.SC and CAR.DE = DAY.DT
WHERE CAR.D2 <> DAY.DY
AND CAR.SC in ( 50 )
- LEA: UPDATE LEA SET LEA.DY = DAY.DY From LEA INNER JOIN DAY on LEA.SCL = DAY.SC and LEA.DT = DAY.DT
WHERE LEA.DY <> DAY.DY and LEA.DY >= 0 and LEA.DY <= 300 and DAY.DEL = 0 AND LEA.SCL in ( 50 )