Various fixes for course/class tables - LWCA

fixCourseClassGovLWCA.sql

------------------------------------------------------------------------------------


--first term
update Class 
set iLV_SessionID = 258
where iclassid in
		(select iclassid from Class 
		where iClassID in 
				(select c.iclassid from class c	--all term 1 JR/HS classes
				join ClassResource cr
					on cr.iClassID = c.iClassID
				join ClassSchedule cs
					on cs.iClassResourceID = cr.iClassResourceID
				where cs.itermid = 1)
		AND iClassID NOT in 
				(select c.iclassid from class c	--all term 2 JR/HS classes
				join ClassResource cr
					on cr.iClassID = c.iClassID
				join ClassSchedule cs
					on cs.iClassResourceID = cr.iClassResourceID
				where cs.itermid = 2))


--second term
update Class 
set iLV_SessionID = 259
where iclassid in
		(select iclassid from Class 
		where iClassID in 
				(select c.iclassid from class c	--all term 1 JR/HS classes
				join ClassResource cr
					on cr.iClassID = c.iClassID
				join ClassSchedule cs
					on cs.iClassResourceID = cr.iClassResourceID
				where cs.itermid = 2)
		AND iClassID NOT in 
				(select c.iclassid from class c	--all term 2 JR/HS classes
				join ClassResource cr
					on cr.iClassID = c.iClassID
				join ClassSchedule cs
					on cs.iClassResourceID = cr.iClassResourceID
				where cs.itermid = 1))
				

--all remaining classes
update Class set iLV_SessionID = 260
where iLV_SessionID = ''

------------------------------------------------------------------------------------



--hope to find which courses are high school based on grades of enrolled students
update course
set iDepartmentExamsID = 2
from Student s
join Enrollment e
	on e.iStudentID = s.iStudentID
join Class cl
	on cl.iClassID = e.iClassID
join Course co
	on cl.icourseid = co.iCourseID
where s.iGradesID in (10,11,12)
and co.iDepartmentExamsID != 1


--hope to find which courses are junior high school based on grades of enrolled students
update course
set iDepartmentExamsID = 3
from Student s
join Enrollment e
	on e.iStudentID = s.iStudentID
join Class cl
	on cl.iClassID = e.iClassID
join Course co
	on cl.icourseid = co.iCourseID
where s.iGradesID not in (10,11,12)


--elem
update course
set iDepartmentExamsID = 3
from course co
join class cl
	on co.icourseid = cl.icourseid
where cl.itrackid in (3,4,5)



------------------------------------------------------------------------------------



--update government codes on courses table (rest will have to be done manually)
update Course
set iGovCourseID = gc.iGovCourseID
from course c
join GovCourse gc
	on gc.cCSRID = ltrim(rtrim(c.cGovernmentCode))



------------------------------------------------------------------------------------



--fixgrades
update course set iLow_GradesID = 13
update Course set iHigh_GradesID = 12



------------------------------------------------------------------------------------



--update class and make it match course department exam
update Class 
set iDepartmentExamsID = co.iDepartmentExamsID
from Class cl
join course co
	on cl.icourseid = cl.iCourseID