Standards Push Scripts
Filter
Obsolete & Delete
Rename
SET
Filtering Standards by ID
DELETE FROM StateStandardStandardFlag_TBL WHERE StandardFlagID = 00000000 AND YearID = @YearID AND StateStandardID NOT in (SELECT StateStandardID FROM StateStandards_TBL WHERE YearID = @YearID AND Status = 'Custom') INSERT INTO StateStandardStandardFlag_TBL (StandardFlagID, StateStandardID, YearID) SELECT 00000000, StateStandardID, @YearID FROM StateStandards_TBL WHERE DistrictID in (00000000) AND YearID = @YearID AND StateStandardID NOT in (SELECT StateStandardID FROM StateStandards_TBL WHERE YearID = @yearID AND Status = 'Custom')
Filtering Standards by CA Name
DELETE FROM StateStandardStandardFlag_TBL WHERE StandardFlagID = 00000000 AND YearID = @YearID AND StateStandardID NOT in (SELECT StateStandardID FROM StateStandards_TBL WHERE YearID = @YearID AND Status = 'Custom') INSERT INTO StateStandardStandardFlag_TBL (StandardFlagID, StateStandardID, YearID) SELECT 00000000, StateStandardID, @YearID FROM StateStandards_TBL WHERE ContentareaID in (SELECT ContentareaID FROM Contentareas_TBL WHERE DistrictID = 00000000 AND Contentarea LIKE '%name%' AND YearID = @YearID) AND YearID = @YearID AND StateStandardID NOT in (SELECT StateStandardID FROM StateStandards_TBL WHERE YearID = @YearID AND Status = 'Custom')
Filtering Standards by CSA Name
DELETE FROM StateStandardStandardFlag_TBL WHERE StandardFlagID = 00000000 AND YearID = @YearID AND StateStandardID NOT in (SELECT StateStandardID FROM StateStandards_TBL WHERE YearID = @YearID AND Status = 'Custom') INSERT INTO StateStandardStandardFlag_TBL (StandardFlagID, StateStandardID, YearID) SELECT 00000000, StateStandardID, @YearID FROM StateStandards_TBL WHERE ContentsubareaID in (SELECT ContentsubareaID FROM Contentsubareas_TBL WHERE DistrictID = 00000000 AND Contentsubarea LIKE '%name%' AND YearID = @YearID) AND YearID = @YearID AND StateStandardID NOT in (SELECT StateStandardID FROM StateStandards_TBL WHERE YearID = @YearID AND Status = 'Custom')
Filtering Standards by SSL Name
DELETE FROM StateStandardStandardFlag_TBL WHERE StandardFlagID = 00000000 AND YearID = @YearID AND StateStandardID NOT in (SELECT StateStandardID FROM StateStandards_TBL WHERE YearID = @YearID AND Status = 'Custom') INSERT INTO StateStandardStandardFlag_TBL (StandardFlagID, StateStandardID, YearID) SELECT 00000000, StateStandardID, @YearID FROM StateStandards_TBL WHERE StatestandardlevelID in (SELECT StatestandardlevelID FROM Statestandardlevels_TBL WHERE DistrictID = 00000000 AND Leveltitle LIKE '%name%' AND YearID = @YearID) AND YearID = @YearID AND StateStandardID NOT in (SELECT StateStandardID FROM StateStandards_TBL WHERE YearID = @YearID AND Status = 'Custom')
Filtering Standards by SS Name
DELETE FROM StateStandardStandardFlag_TBL WHERE StandardFlagID = 00000000 AND YearID = @YearID AND StateStandardID NOT in (SELECT StateStandardID FROM StateStandards_TBL WHERE YearID = @YearID AND Status = 'Custom') INSERT INTO StateStandardStandardFlag_TBL (StandardFlagID, StateStandardID, YearID) SELECT 00000000, StateStandardID, @YearID FROM StateStandards_TBL WHERE DistrictID = 00000000 AND YearID = @YearID AND ContentareaID in (00000000) AND Statestandard LIKE 'name1%' COLLATE SQL_Latin1_General_CP1_CS_AS AND Statestandard LIKE 'name2%' COLLATE SQL_Latin1_General_CP1_CS_AS AND StateStandardID NOT in (SELECT StateStandardID FROM StateStandards_TBL WHERE Status='Custom' AND YearID = @YearID) AND StateStandardID NOT in (SELECT StateStandardID FROM StateStandards_TBL WHERE YearID = @YearID AND Status = 'Custom')
Making Standards Obsolete or Unobsolete
UPDATE Statestandards_TBL SET bitOBSOLETE = 1 WHERE ContentareaID in (00000000) AND YearID = @YearID
Pushing All but Certain Contentarea(s)
DELETE FROM Statestandards_TBL WHERE DistrictID = 00000000 AND ContentareaID in (00000000) AND YearID = @YearID DELETE FROM Statestandardlevels_TBL WHERE DistrictID = 00000000 AND ContentareaID in (00000000) AND YearID = @YearID DELETE FROM Contentsubareas_TBL WHERE DistrictID = 00000000 AND ContentareaID in (00000000) AND YearID = @YearID DELETE FROM Contentareas_TBL WHERE DistrictID = 00000000 AND ContentareaID in (00000000) AND YearID = @YearID
Pushing All but Certain Benchmarklevel(s)
DELETE FROM Statestandards_TBL WHERE DistrictID = 00000000 AND BenchmarklevelID in (00000000) AND YearID = @YearID DELETE FROM Statestandardlevels_TBL WHERE DistrictID = 00000000 AND BenchmarklevelID in (00000000) AND YearID = @YearID DELETE FROM Benchmarklevels_TBL WHERE DistrictID = 00000000 AND BenchmarklevelID in (00000000) AND YearID = @YearID
Renaming Contentarea
UPDATE Contentareas_TBL SET Contentarea = 'The New Name' WHERE DistrictID = 00000000 AND ContentareaID = 00000000 AND YearID = @YearID
Renaming Contentsubarea
UPDATE Contentsubareas_TBL SET Contentsubarea = 'The New Name' WHERE DistrictID = 00000000 AND ContentsubareaID = 00000000 AND YearID = @YearID
Renaming Benchmarklevel
UPDATE Benchmarklevels_TBL SET Benchmarklevel = 'The New Name' WHERE DistrictID = 00000000 AND BenchmarklevelID = 00000000 AND YearID = @YearID
Renaming StateStandardlevel
UPDATE Statestandardlevels_TBL SET Leveltitle = 'The New Name' WHERE DistrictID = 00000000 AND StatestandardlevelID = 00000000 AND YearID = @YearID
Renaming StateStandardlevel
UPDATE statestandards_TBL SET Statestandard = 'The New Name' WHERE DistrictID = 00000000 AND StatestandardID = 00000000 AND YearID = @YearID
Renaming 5-level Structure
UPDATE StateStandardlevelsbase_TBL SET Label = CASE WHEN NextLevel LIKE 'Contentarea%' THEN 'New Content Area Name' WHEN NextLevel LIKE 'Contentsubarea%' THEN 'New Content Sub Area Name' WHEN NextLevel LIKE 'Benchmarklevel%' THEN 'New Benchmark Level Name' WHEN NextLevel LIKE 'Statestandardlevel%' THEN 'New State Standard Level Name' WHEN NextLevel LIKE 'Statestandard%' THEN 'New State Standard Name' END WHERE YearID = @YearID AND DistrictID = 00000000
Giving Custom Status (Push to SET)
UPDATE Contentareas_TBL SET Status = 'CUSTOM', bCUSTOM = 1 WHERE DistrictID = 00000000 AND YearID = @YearID UPDATE Benchmarklevels_TBL SET Status = 'CUSTOM', bCUSTOM = 1 WHERE DistrictID = 00000000 AND YearID = @YearID UPDATE Contentsubareas_TBL SET Status = 'CUSTOM' WHERE DistrictID = 00000000 AND YearID = @YearID UPDATE Statestandardlevels_TBL SET Status = 'CUSTOM' WHERE DistrictID = 00000000 AND YearID = @YearID UPDATE Statestandards_TBL SET Status = 'CUSTOM' WHERE DistrictID = 00000000 AND YearID = @YearID
Removing Custom Status
UPDATE Contentareas_TBL SET Status = 'NULL', bCUSTOM = 0 WHERE DistrictID = 1 AND ContentareaID in (00000000) AND YearID = @YearID UPDATE Benchmarklevels_TBL SET Status = 'NULL', bCUSTOM = 0 WHERE DistrictID = 1 AND BenchmarklevelID in (00000000) AND YearID = @YearID UPDATE Contentsubareas_TBL SET Status = 'NULL' WHERE DistrictID = 1 AND ContentareaID in (00000000) AND YearID = @YearID UPDATE Statestandardlevels_TBL SET Status = 'NULL' WHERE DistrictID = 1 AND ContentareaID in (00000000) AND YearID = @YearID UPDATE Statestandards_TBL SET Status = 'NULL' WHERE DistrictID = 1 AND ContentareaID in (00000000) AND YearID = @YearID
Moving CSA(s) to Another CA
UPDATE Contentsubareas_TBL SET ContentareaID = 'where you want it to go' WHERE YearID = @YearID AND ContentareaID = 'where it current is' UPDATE Statestandards_TBL SET ContentareaID = 'where you want it to go' WHERE YearID = @YearID AND ContentareaID = 'where it current is' UPDATE Statestandardlevels_TBL SET ContentareaID = 'where you want it to go' WHERE YearID = @YearID AND ContentareaID = 'where it current is'
Moving Standard(s) to Another BL
UPDATE Statestandardlevels_TBL SET BenchmarklevelID = 'where you want it to go' WHERE YearID = @YearID AND BenchmarklevelID = 'where it current is' AND ContentareaID = 00000000 UPDATE Statestandards_TBL SET BenchmarklevelID = 'where you want it to go' WHERE YearID = @YearID AND BenchmarklevelID = 'where it current is' AND ContentareaID = 00000000