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 all school sites being fixed utilize the same Calendar and thus the correction can be made using the District level Calendar and pushing those changes to the affected sites via SQL.


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
SQL


Step 2: Create the new Calendar at School 0


Step 3: Delete the DAY table for the desired schools

  • Replace the values in parenthesis with the desired School Codes
DELETE DAY
WHERE SC IN (801, 101, 76, 1, 24, 102, 100)
SQL

Step 4: Insert the new DAY records

  • Replace the values in parenthesis following LOC.CD with the same School Codes from Step 3
INSERT INTO [DAY] ( SC, DY, DT, HO, MO, T1, T2, T3, T4, T5, T6, AP, EN, BL, AB, ML, T7, T8, T9, T10, T11, T12, T13,     T14, T15, T16, T17, T18, T19, T20, T21, T22, T23, T24, T25, T26,     PD, WC, W1, W2, W3, W4, W5, W6, W7, W8, W9, W10, W11, W12, W13,     W14, W15, W16, W17, W18, W19, W20, W21, W22, W23, W24, W25, W26, WM,     M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12, M13,     M14, M15, M16, M17, M18, M19, M20, M21, M22, M23, M24, M25, M26, BS )
SELECT LOC.CD, DAY.DY, DAY.DT, DAY.HO, DAY.MO, DAY.T1, DAY.T2, DAY.T3, DAY.T4, DAY.T5, DAY.T6, DAY.AP, DAY.EN, DAY.BL, DAY.AB, DAY.ML,     DAY.T7, DAY.T8, DAY.T9, DAY.T10, DAY.T11, DAY.T12, DAY.T13,     DAY.T14, DAY.T15, DAY.T16, DAY.T17, DAY.T18, DAY.T19, DAY.T20, DAY.T21, DAY.T22, DAY.T23, DAY.T24, DAY.T25, DAY.T26,     DAY.PD, DAY.WC, DAY.W1, DAY.W2, DAY.W3, DAY.W4, DAY.W5, DAY.W6, DAY.W7, DAY.W8, DAY.W9, DAY.W10, DAY.W11, DAY.W12, DAY.W13,     DAY.W14, DAY.W15, DAY.W16, DAY.W17, DAY.W18, DAY.W19, DAY.W20, DAY.W21, DAY.W22, DAY.W23, DAY.W24, DAY.W25, DAY.W26, DAY.WM,     DAY.M1, DAY.M2, DAY.M3, DAY.M4, DAY.M5, DAY.M6, DAY.M7, DAY.M8, DAY.M9, DAY.M10, DAY.M11, DAY.M12, DAY.M13,     DAY.M14, DAY.M15, DAY.M16, DAY.M17, DAY.M18, DAY.M19, DAY.M20, DAY.M21, DAY.M22, DAY.M23, DAY.M24, DAY.M25, DAY.M26, DAY.BS
FROM DAY, LOC
WHERE DAY.SC = 0 and DAY.DEL = 0 and LOC.DEL = 0
and LOC.CD in (801, 101, 76, 1, 24, 102, 100)
SQL

Step 5: Correct corresponding tables with the new Day numbers

In each script, replace the values in parenthesis with the same School Codes from Step 3

  • 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 (801, 101, 76, 1, 24, 102, 100)
SQL


  • 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 (801, 101, 76, 1, 24, 102, 100)
SQL
  • 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 (801, 101, 76, 1, 24, 102, 100)
SQL


  • 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 (801, 101, 76, 1, 24, 102, 100)
SQL