SchoolLogic database sanity checks

sanity checks

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









	select 'Demographics Area'


	BEGIN

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


	select 'Find students with iTrackID=0 that are currently enrolled.' union select 'Should be empty.'
	select * from student where iTrackID = 0 and lcurrent = 1 and iStatusType = 1

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

	select 'Find students with an iTrackid=0 that have a current StudentStatus line' union select 'Should be empty.'
	select * from Student
	join StudentStatus on student.iActive_StudentStatusID = studentstatus.iStudentStatusID 
	where (studentstatus.dOutDate = '' and studentstatus.iLV_OutStatusValueID = '')
	and student.iTrackID = 0

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

	select 'Find students that are Withdrawn or History that still have an iTrackID filled in' union select 'Should be empty'
	select * from Student s 
	where (s.lCurrent = 0 OR s.iStatusType = 3)
	AND s.iTrackID != 0

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

	select 'Find more than one student sharing the same Government Number (other than blank)' union select 'Should be empty.'
	select * from student where cGovernmentNumber in 
	 (select cgovernmentnumber from Student
	 where cGovernmentNumber not like ''
	 group by cGovernmentNumber
	 having COUNT(*) > 1)
	order by student.cGovernmentNumber, student.cLastName , student.cfirstname

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

	select 'Find students existing twice (by firstname, lastname, gov number)' union select 'Should be empty.'
	select
	 a.cLastName as 'Last Name', 
	 a.cFirstName as 'First Name', 
	 a.cGovernmentNumber as 'School A Government Number', 
	 b.cgovernmentnumber as 'School B Government Number', 
	 SchoolA.cName as 'School A', 
	 SchoolB.cname as 'School B'
	from Student a
	 join Student b 
	 on a.dBirthdate like b.dBirthdate 
	join School SchoolA
	 on a.iSchoolID = SchoolA.ischoolid
	join School SchoolB
	 on b.iSchoolID = SchoolB.ischoolid
	where a.cFirstName like b.cfirstname
	and a.iStudentID =
	 (select max(istudentid) from student 
	  where cLastName like a.cLastName 
	  and cFirstName like a.cFirstName )
	and a.cLastName like b.cLastName
	and a.iSchoolID != b.ischoolid
	and a.cGovernmentNumber != b.cGovernmentNumber
	and (a.lCurrent = 1 or b.lcurrent = 1)
	order by a.clastname, a.cfirstname





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





		BEGIN
		
			
		select 'Demographics -> Personal Area'
		
		select 'Find student email addresses with invalid format' union select ''
		select iStudentID,cLastName,cFirstName,cStudentNumber,mEmail from student where mEmail !='' and mEmail NOT LIKE ('_%@__%.__%')
		order by clastname,cfirstname

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

		select 'Find contact email addresses with invalid format' union select ''
		select rtrim(student.clastname)+', ' + rtrim(student.cFirstName) as cStudent ,Contact.iContactID,Contact.cLastName,contact.cFirstName,contact.mEmail
		from contact
			inner join ContactRelation on ContactRelation.iContactID = Contact.iContactID
			inner join student on student.istudentid = contactrelation.istudentID
		where contact.mEmail !='' and contact.mEmail NOT LIKE ('_%@__%.__%')
		order by student.clastname,student.cfirstname,contact.clastname,contact.cfirstname

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

		select 'Find students with the same iFamilyID but different lastnames and addresses' union select 'this is a sanity check'
		select a.cStudentNumber, a.clastname, a.cFirstName, b.cStudentNumber, b.cLastName, b.cfirstname, al.cstreet, bl.cstreet, a.ifamilyid, b.ifamilyid, '' as ' ', * from Student a
		join Student b on a.iFamilyID = b.iFamilyID
		join Location al on a.iLocationID = al.iLocationID 
		join Location bl on b.iLocationID = bl.ilocationid
		and a.cLastName != b.cLastname
		and b.iFamilyID != 0
		and a.iFamilyID != 0
		and al.cStreet != bl.cstreet

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

		select 'Find students with the same location but different iFamilyID' union select 'this is a sanity check'
		select s1.iLocationID, * from Student s1
		where iFamilyID !=  
		(select AVG(iFamilyID) from Student s2 
		  where iLocationID = s1.iLocationID)
		order by s1.iLocationID, s1.clastname

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

		select 'Find locations not linked to anything' union select 'the migration cleanup should remove these'
		select * from location
		where iLocationID not in (select iLocationID from Contact)	
		and iLocationID not in (select iLocationID from ContactAddress)
		and iLocationID not in (select iLocationID from Student)
		and iLocationID not in (select iLocationID from StudentAddress)
		
		---------------------------------------------------------------------------------------------------------------------------------------	

		select 'Find blank country values (which results in bad phone number formatting)' union select 'should be empty'
		select * from Student
		join Location on student.ilocationid = location.ilocationid
		where location.iCountryID = 0

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

		select 'Find mostly blank studentaddress records (which are probably pointless and can be cleaned up' union select 'hopefully this is empty'
		select * from StudentAddress
		join location on location.iLocationID = studentaddress.ilocationid 
		where studentaddress.ilocationid in
			(select ilocationid from location
			 where cApartment =''
			 and cHouseNo = ''
			 and cStreet = ''
			 and iLV_QuadrantID =0
			 and iLV_CityID =0
			 and iLV_QuadrantID =0
			 and iLV_RegionID =0
			 and cPostalCode = ''
			 and cPhone =''
			 and lunlistedphone =0)

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

		select 'Find how many MultiComm records are associated with a student.' union select 'large numbers should be checked for issues'
		select count(*), iStudentID from studentcommunication
		group by istudentid
		order by COUNT(*) DESC

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

		--end Demographics->Personal
		END





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






		BEGIN

		select 'Demographics -> Contact Area'
		
		---------------------------------------------------------------------------------------------------------------------------------------	
		
		select 'Find how many MultiComm records are associated with a contact.' union select 'large numbers should be checked for issues'	
		select count(*), iContactID from ContactCommunication 
		group by iContactID
		order by COUNT(*) DESC
			
		---------------------------------------------------------------------------------------------------------------------------------------	
		
		select 'Check for "Lives With" contacts linked to a different location than the student they are related to.' union select 'should be blank'
		select COUNT(*), contact.ilocationid, contact.iContactID, student.istudentid from Contact 
		join ContactRelation 
			on contact.iContactID = ContactRelation.iContactID 
		join Student 
			on student.iStudentID = contactrelation.iStudentID
		where contactrelation.lLivesWithStudent = 1
		and contact.iLocationID != student.iLocationID 
		group by contact.ilocationid, contact.icontactid, student.istudentid
		having COUNT(*) > 1
		
		---------------------------------------------------------------------------------------------------------------------------------------	
		---------------------------------------------------------------------------------------------------------------------------------------	

		--end Demographics -> Contact
		END



	--end Demographics
	END








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








	BEGIN

	select 'Status Area'

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

	--NOTE: I've been informed this is not how outside students work. they dont have to have an iOutside_TrackID.
	--
	--select 'Find students that are Current and Outside and have a Current Outside Status with no Track.' union select 'Should be blank.'
	--select s.istudentid, MAX(dInDate) as 'MAX(dInDate)', ss.dOutDate, ss.lOutsideStatus, ss.iOutside_TrackID, ss.ischoolid  from studentstatus ss
	--join Student s on s.iStudentID = ss.iStudentID 
	--where s.lCurrent = 1
	--and ss.lOutsideStatus = 1
	--and ss.iOutside_TrackID = 0
	--group by s.istudentid, s.istudentid, ss.dOutDate, ss.lOutsideStatus, ss.iOutside_TrackID, ss.ischoolid

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

	select 'Find students with an iActive_StudentStatusID that isn''t the most recent status' union select 'This ignores Outside statuses.' union select 'Should be blank.'
	select * from Student s
	where 1 not like (select top 1 lOutsideStatus from StudentStatus ss where iStudentID = s.iStudentID order by dInDate DESC)
	and s.iActive_StudentStatusID not like  
		(select top 1 istudentstatusid from StudentStatus ss where iStudentID = s.iStudentID order by dInDate DESC)
	 
	---------------------------------------------------------------------------------------------------------------------------------------	
	 
	select 'Find students with an iActive_StudentStatusID that doesnt refer to an actual status line' union select 'Should be blank.'
	select * from Student where iActive_StudentStatusID not in (select istudentstatusid from StudentStatus)

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

	select 'Find students with an InStatus or OutStatus value that isn''t from the "Currently Enrolled" or "Not Enrolled" tables, respectively.' union select 'Should be blank.' 
	select * from studentstatus
	where iLV_InStatusValueID != 0
	and iLV_InStatusValueID not in 
		(select ilookupvaluesid from LookupValues where iDataTypesID like
			(select iDataTypesID from Datatypes where cName like 'Currently Enrolled'))
	select * from studentstatus
	where iLV_OutStatusValueID != 0
	and iLV_OutStatusValueID not in 
		(select ilookupvaluesid from LookupValues where iDataTypesID like
			(select iDataTypesID from Datatypes where cName like 'Not Enrolled'))

	---------------------------------------------------------------------------------------------------------------------------------------	
	
	select 'Find students that are marked Historical yet are still enrolled in the school.' union select 'Looks at iStatusType field.' union select 'Should be blank.'
	select * from student where lCurrent = 0 and iStatusType = 1

	---------------------------------------------------------------------------------------------------------------------------------------	
	
	select 'Find students that are marked Historical yet are still enrolled in the school.' union select 'Looks at Student Status records.' union select 'Should be blank.'
	select * from student
	join studentstatus on student.iStudentID = studentstatus.iStudentID
	where studentstatus.dOutDate = '' 
	and studentstatus.iLV_OutStatusValueID = ''
	and student.iActive_StudentStatusID = studentstatus.iStudentStatusID
	and student.lCurrent = 0

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

	select 'Find duplicate student status records.' union select 'Should be blank.'
	select * from StudentStatus
	where iStudentStatusID not in 
		(select MAX(iStudentStatusID) from StudentStatus
		 group by iStudentID, dInDate, dOutDate, iLV_VerificationID, lOutsideStatus, iSchoolID, iLV_InStatusValueID, iLV_OutStatusValueID, iOutside_TrackID)



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


	--end Status 
	END







	---------------------------------------------------------------------------------------------------------------------------------------	
	---------------------------------------------------------------------------------------------------------------------------------------	
	---------------------------------------------------------------------------------------------------------------------------------------	
	---------------------------------------------------------------------------------------------------------------------------------------	
	
	
	
	
	
	
	BEGIN
	
	select 'Attendance  Area'
	
	---------------------------------------------------------------------------------------------------------------------------------------	
	---------------------------------------------------------------------------------------------------------------------------------------	
	
	select 'Find Attendance records linked to an EnrollmentID that doesn''t exist in the Enrollment table AND the student is enrolled in that class on the date the attendance was taken.' union select 'Sanity check.'
	select * from Attendance a
	join Student s
		on a.iStudentID = s.iStudentID
	join Enrollment e
		on ( (e.iStudentID = s.iStudentID) AND (a.iClassID = e.iClassID) AND (a.iSchoolID = e.iSchoolID) )
	where a.iEnrollmentID not in 
		(select iEnrollmentID from Enrollment)
	and (a.dDate >= e.dInDate)
	and (a.dDate <= e.dOutDate) 
	
	---------------------------------------------------------------------------------------------------------------------------------------	
	---------------------------------------------------------------------------------------------------------------------------------------	
	
	--end Attendance  	
	END
	
	
	
	
	
	
	
	
	
	---------------------------------------------------------------------------------------------------------------------------------------	
	---------------------------------------------------------------------------------------------------------------------------------------	
	---------------------------------------------------------------------------------------------------------------------------------------	
	---------------------------------------------------------------------------------------------------------------------------------------	
	
	
	
	
	
	
	
	BEGIN
	
	
	select 'Fees Area'
	
	---------------------------------------------------------------------------------------------------------------------------------------	
	---------------------------------------------------------------------------------------------------------------------------------------	
	
	select 'Find Fees linked to Classes that don''t exist' union select 'Should be blank.'
	select * from Fees 
	where iClassID not in 
		(select iClassID from Class)
	and iClassID not like 0
	
	---------------------------------------------------------------------------------------------------------------------------------------	
	
	select 'Find fees linked to a Class from a different school than the Fee is from' union select 'Should be blank.'
	select * from Fees 
	join Class 
		on class.iClassID = fees.iClassID 
	where class.iSchoolID != fees.iSchoolID 
	
	---------------------------------------------------------------------------------------------------------------------------------------	
	
	select 'Find StudentPayments linked to StudentPayDetails with a different iSchoolID' union select 'Should be blank.'
	select * from StudentPayment
	join StudentPayDetails 
		on studentpayment.iStudentPaymentID = StudentPayDetails.iStudentPaymentiD 
	where studentpayment.iSchoolID != StudentPayDetails.iSchoolID 
	
	---------------------------------------------------------------------------------------------------------------------------------------	

	
	
	---------------------------------------------------------------------------------------------------------------------------------------	
	---------------------------------------------------------------------------------------------------------------------------------------	
	
	
	--end Fees
	END
	
	
	
	
	
	
	
	---------------------------------------------------------------------------------------------------------------------------------------	
	---------------------------------------------------------------------------------------------------------------------------------------	
	---------------------------------------------------------------------------------------------------------------------------------------	
	---------------------------------------------------------------------------------------------------------------------------------------	
	
	
	
	
	
	BEGIN
	
	select 'Schedule Area'
	
	
	
	---------------------------------------------------------------------------------------------------------------------------------------	
	---------------------------------------------------------------------------------------------------------------------------------------	
			
		BEGIN
		
		select 'Student Enrollments Area'
		
		---------------------------------------------------------------------------------------------------------------------------------------	
		
		select 'Find Enrollment record linked to Classes that don''t exist' union select 'Should be blank.'
		select * from enrollment 
		where iclassid not in 
			(select iclassid from class) 
		
		---------------------------------------------------------------------------------------------------------------------------------------	
		
		select 'Find Enrollment record linked to Students that don''t exist' union select 'Should be blank.'
		select * from enrollment 
		where iStudentID not in 
			(select iStudentID from student)
		
		---------------------------------------------------------------------------------------------------------------------------------------	
		
		select 'Find Enrollment records with a Blank In-Date' union select 'Should be blank.'
		select * from Enrollment where dInDate = '' 
		
		---------------------------------------------------------------------------------------------------------------------------------------	
		
		select 'Find Enrollment records where the OutDate occurs before the InDate' union select 'Should be blank.'
		select * from Enrollment where dOutDate < dInDate and dOutDate != ''
		
		---------------------------------------------------------------------------------------------------------------------------------------	
		
		select 'Find Enrollment records with a Completion Status but no OutDate, or an OutDate but no Completion Status.' union select 'Should be blank.'
		select * from Enrollment 
		where (iLV_CompletionStatusID != '' and dOutDate = '' ) 
		or (dOutDate != '' and iLV_CompletionStatusID = '') 
		order by iLV_CompletionStatusID 
		
		---------------------------------------------------------------------------------------------------------------------------------------	
		
		--im finding that the order of grades is messed up in a lot of DB's.
		select 'Find students enrolled in classes that have a range outside of the student''s grade.' union select 'Should be blank.'
		select 
			dbo.GetGradesName(sg.iGradesID) as 'Student Grade', sg.iorder, 
			dbo.GetGradesName(clg.iGradesID) as 'Class Low Grade', cLg.iOrder, 
			dbo.GetGradesName(cHg.iGradesID) as 'Class High Grade', cHg.iOrder,
			* from Enrollment e
		join Student s
			on s.iStudentID = e.iStudentID 
		join Class c
			on e.iClassID = c.iclassid
		join Grades sg
			on s.iGradesID = sg.iGradesID  --and s.iSchoolID = sg.iSchoolID
		join Grades cLg
			on c.iLow_GradesID = cLg.iGradesID --and (c.iSchoolID = cLg.iSchoolID or cLg.iSchoolID = 0)
		join Grades cHg
			on c.iHigh_GradesID = cHg.iGradesID --and (c.iSchoolID = cHg.iSchoolID OR cHg.iSchoolID = 0)
		where (sg.iOrder < cLg.iOrder) OR (sg.iOrder > cHg.iOrder)
			
		---------------------------------------------------------------------------------------------------------------------------------------	
		
		--end Student Enrollments
		END 
		
		
		
		
		---------------------------------------------------------------------------------------------------------------------------------------	
		---------------------------------------------------------------------------------------------------------------------------------------	
		
		
		
		
		BEGIN
		
		select 'Class Setup and Schedule Area'
				
		---------------------------------------------------------------------------------------------------------------------------------------	
		
		select 'Find ClassPeriod records linked to Classes that don''t exist.' union select 'Should be blank.'
		select * from classperiod 
		where iClassID not in 
			(select iClassID from Class) 
		
		---------------------------------------------------------------------------------------------------------------------------------------	
		
		select 'Find ClassPeriod records linked to ReportPeriods that don''t exist.' union select 'Should be blank.'
		select * from classperiod 
		where ireportperiodid not in 
			(select iReportPeriodID from ReportPeriod)
		
		---------------------------------------------------------------------------------------------------------------------------------------	
		
		select 'Find ClassTerm records linked to Classes that don''t exist.' union select 'Should be blank.'
		select * from classterm 
		where iClassID  not in 
			(select iClassiD from Class) or itermid not in (select itermid from term)
		
		---------------------------------------------------------------------------------------------------------------------------------------	
		
		select 'Find ClassTerm records linked to Terms that don''t exist.' union select 'Should be blank.'
		select * from classterm 
		where itermid not in 
			(select itermid from term)
		
		---------------------------------------------------------------------------------------------------------------------------------------	
		
		select 'Find Classes with a Grade Range outside of what is defined on the associated Course' union select 'Should be blank.'
		select dbo.getGradesName(class.iLow_GradesID) as 'Class Low Grade', dbo.GetGradesName(class.iHigh_GradesID) as 'Class High Grade', dbo.GetGradesName(course.iLow_GradesID) as 'Course Low Grade',  dbo.GetGradesName(course.iHigh_GradesID) as 'Course High Grade', * from class
		join Course 
			on course.iCourseID = Class.iclassid
		where (class.iLow_GradesID < course.iLow_GradesID) 
		or (class.iHigh_GradesID > course.iHigh_GradesID)
		
		---------------------------------------------------------------------------------------------------------------------------------------	
		
		
		select 'Find Classes linked to Courses from other schools' union select 'Should be blank.'
		select class.iSchoolID, course.iSchoolID, * from class
		join Course 
			on course.iCourseID = class.iCourseID 
		where (class.iSchoolID != course.iSchoolID AND course.iSchoolID != 0)
		
		---------------------------------------------------------------------------------------------------------------------------------------	
		
		select ' Find Classes linked to Tracks from other schools' union select 'Should be blank.'
		select class.iSchoolID, track.iSchoolID, * from Class
		join Track 
			on track.iTrackID = class.iTrackID 
		where track.iSchoolID != class.ischoolid
		
		---------------------------------------------------------------------------------------------------------------------------------------	
		
		select 'Find Classes with a value in nFee but no associated record on the Fees table.' union select 'Should be blank.'
		select * from class
		where class.nFee > 0
		and class.iClassID not in 
			(select iClassID from Fees)
			
		---------------------------------------------------------------------------------------------------------------------------------------	
		
		select 'Find associated Class and ClassResource records that DO NOT have the same iSchoolID.' union select 'Should be blank.'
		select classresource.*, class.* from Class
		join ClassResource 
			on ClassResource.iClassID = class.iClassID 
		where (classresource.iSchoolID = 0 
		OR classresource.iSchoolID != class.iSchoolID)
		
		---------------------------------------------------------------------------------------------------------------------------------------	
		
		--NOTE -- WE DONT CARE ABOUT ISCHOOLID OF 0. board level staff is gfood.
		select 'Find Class or ClassResource records linked to a Room from a different School (or an iSchoolID of 0)' union select 'Should be blank.'
		select classresource.*, class.* from Class
		join ClassResource 
			on ClassResource.iClassID = Class.iClassID
		join Room 
			on ClassResource.iRoomID = Room.iRoomID 
		where (room.ischoolid = 0 
		OR room.iSchoolID != ClassResource.iSchoolID 
		OR room.iSchoolID != class.iSchoolID)
		
		---------------------------------------------------------------------------------------------------------------------------------------	
		
		select 'Find Class or ClassResource records linked to a Staff from a different School (or an iSchoolID of 0)' union select 'Should be blank.'
		select staff.ischoolid, classresource.ischoolid, class.ischoolid, classresource.*, class.* from Class
		join ClassResource 
			on ClassResource.iClassID = class.iClassID
		join Staff 
			on staff.iStaffID = ClassResource.iStaffID 
		where (staff.iSchoolID = 0 
			OR ClassResource.iSchoolID != staff.iSchoolID 
			OR class.iSchoolID != staff.iSchoolID)
		AND staff.iSchoolID != 0
			
		---------------------------------------------------------------------------------------------------------------------------------------	
					
		
		--end Class Setup and Schedule
		END
	
		---------------------------------------------------------------------------------------------------------------------------------------	
		---------------------------------------------------------------------------------------------------------------------------------------
	
	
		BEGIN
		
		select 'NYR Scheduling Area'
		
		---------------------------------------------------------------------------------------------------------------------------------------
		
		select 'Find NYR tables with values of 0' union select 'Should be blank.'
		select * from NYClassTerm where  ischoolid=0 or iMBoardID=0 or iClassID=0 or iClassTermID=0 or itermid=0 
		select * from NYClassPeriod where iSchoolID=0 or iClassPeriodID=0 or iClassID=0 or iReportPeriodID=0 or iMBoardID=0
		select * from MBoard where iSchoolID=0 or iMBoardID=0
		select * from NYClass where ISCHOOLID=0 or iClassID=0 or ICOURSEID=0 or ITRACKID=0 or iMBoardID=0
		select * from track where iSchoolID=0
	
		---------------------------------------------------------------------------------------------------------------------------------------
	
		--end NYR Scheduling
		END
	
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	
	
	
	--end Schedule 
	END
	
	
	
	
	
	
	
	
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	
	
	
	
	
	
	
	BEGIN
	
	select 'School Area'
	
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	
	select 'Find in-district schools without a registration key'  union select 'Should be blank.'
	select * from school where iDistrictID = 1 and mRegistrationKey = ''
		
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	
	END
	
	
	
	
	
	
	
	
	
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	
	
	
	
	
	
	
	
	
	BEGIN
	
	select 'Courses Area'
		
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	
	select 'Show SubCourses linked to Master Courses where the lMasterCourse isn''t set to "Yes".' union select 'Should be blank.'
	select cMas.cName, cSub.cName, * from course cMas
	join Course cSub 
		on cSub.iMaster_CourseID = cmas.iCourseID 
	where cMas.lMasterCourse = 0
	order by cMas.iCourseID 
	
	---------------------------------------------------------------------------------------------------------------------------------------
	
	select 'Show Courses linked to Government Courses that don''t exist' union select 'Should be blank.'
	select * from course
	where iGovCourseID != 0
	and iGovCourseID not in 
		(select igovcourseid from GovCourse)
	
	---------------------------------------------------------------------------------------------------------------------------------------
	
	select 'Show Courses with Government Course Codes that don''t exist' union select 'Should be blank.'
	select * from course
	where cGovernmentCode not like ''
	and cGovernmentCode not in
		(select cGovernmentCode from GovCourse)
	
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	
	--end Courses
	END
	
	
	
	
	
	
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	
	
	
	
	
	
	
	BEGIN
	
	select 'Users Area'
	
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	
	select 'Find Users linked to Staff that don''t exist.' union select 'Should be blank.'
	select * from users
	where ISTAFFID not in 
		(select ISTAFFID from Staff)
	and ISTAFFID != 0
	
	---------------------------------------------------------------------------------------------------------------------------------------
	
	select 'Find Users linked to Staff that don''t exist.' union select 'Should be blank.'
	select * from USERS
	where ISCHOOLID not in 
		(select ISCHOOLID from Staff)
	and ISCHOOLID != 0
	
	---------------------------------------------------------------------------------------------------------------------------------------
	
	select 'Find Users linked to Staff from a different School.' union select 'Should be blank.'
	select * from USERS
	join Staff 
		on staff.iStaffID = users.ISTAFFID
	where users.ISCHOOLID != staff.iSchoolID 
	
	---------------------------------------------------------------------------------------------------------------------------------------
	
	select 'Find Users with a different Password on their related Staff record.' union select 'Only for "Linked to Staff" users.' union select 'Should be blank.'
	select * from USERS
	join Staff 
		on staff.iStaffID = users.ISTAFFID
	where users.CPASSWORD COLLATE DATABASE_DEFAULT != staff.cPassword COLLATE DATABASE_DEFAULT 
	
	---------------------------------------------------------------------------------------------------------------------------------------
	
	select 'Find Users with a different Username on their related Staff record.' union select 'Only for "Linked to Staff" users.' union select 'Should be blank.'
	select users.CUSERNAME, staff.cUserName, * from users
	join Staff 
		on staff.iStaffID = users.ISTAFFID 
	where staff.cUserName != users.cusername
	and LLOGICUSER = 1
	
	---------------------------------------------------------------------------------------------------------------------------------------
	
	select 'Find any Staff records with more than one User record linked to them.' union select 'Should be blank.' 
	select count(*), Staff.istaffid from users
	join Staff 
		on staff.iStaffID = users.iUsersID
	group by Staff.istaffid
	having COUNT(*) > 1
	order by COUNT(*) DESC
	
	
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	
	--end Users
	END
	
	
	
	
	
	
	
	
	
	
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	
	
	
	
	
	
	
	
	
	BEGIN
	
	select 'Lookup Tables Area'
	
	
	---------------------------------------------------------------------------------------------------------------------------------------

	select 'Find fields not linked to valid areas or datatypes.' union select 'Should be blank.'
	select * from fields
	where 
		(idatatypesid not in 
			(select iDatatypesID from Datatypes) 
		 OR iAreasID not in 
			(select iareasid from areas))
	and iDatatypesID != 0
	and iAreasID != 0
	
	---------------------------------------------------------------------------------------------------------------------------------------
	
	select 'Find lookupvalues not linked to valid datatypes.' union select 'Should be blank.'
	select * from lookupvalues where iDataTypesID not in (select idatatypesid from datatypes)
	
	---------------------------------------------------------------------------------------------------------------------------------------
	
	select 'Find HomeRooms associated with a Room from a different School.' union select 'Should be blank.'
	select iHomeroomID, homeroom.cName, homeroom.iSchoolID as 'HR school', room.iRoomID, Room.iSchoolID as 'Room school'  from HomeRoom 
	join Room 
		on homeroom.iRoomID = room.iRoomID 
	where homeroom.iSchoolID != room.iSchoolID
	
	---------------------------------------------------------------------------------------------------------------------------------------
	
	select 'Check for potential out-of-order grades.' union select 'Sanity check.'
	select * from Grades order by ischoolid, iorder
	
	
	---------------------------------------------------------------------------------------------------------------------------------------
	
	--end Lookup Tables
	END
	
	
	
	
	
	
	
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	
	
	
	
	
	
	BEGIN
	
	select 'Gradebook Area'
	
	
	
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	
	
		BEGIN
		
		select 'Gradebook -> gbUnit Area'
		
		---------------------------------------------------------------------------------------------------------------------------------------
		
		select 'Find gbUnit records where the ReportPeriod has a different iSchoolID than the report, or the iReportPeriodID doesn''t even exist' union select 'Should be blank.'
		select gbunit.iReportPeriodID, * from gbUnit
		full join reportperiod 
			on reportperiod.iReportPeriodID = gbunit.iReportPeriodID 
		where reportperiod.iSchoolID != gbunit.iSchoolID
		or gbunit.iReportPeriodID 
			not in (select iReportPeriodID from ReportPeriod)
		
		---------------------------------------------------------------------------------------------------------------------------------------
		
		select 'Find gbUnit records where the associated Staff doesn''t exist.' union select 'Should be blank.'
		select * from gbUnit where iStaffID 
			not in (select iStaffID from Staff)
			
		---------------------------------------------------------------------------------------------------------------------------------------
		
		select 'Find gbUnit records where the associated Class has a different iSchoolID than the record itself.' union select 'Should be blank.'
		select gbunit.iClassID, gbunit.iSchoolID, class.iSchoolID,  * from gbunit 
		full join class		
			on gbunit.iClassID = class.iClassID
		full join School 
			on gbunit.ischoolid = school.iSchoolID
		where (class.iSchoolID != school.iSchoolID)
		or gbunit.iClassID not in 
			(select iClassID from Class)
			
		---------------------------------------------------------------------------------------------------------------------------------------
		
		--end gbUnit 
		END
		
		
		---------------------------------------------------------------------------------------------------------------------------------------
		---------------------------------------------------------------------------------------------------------------------------------------
		
		
		BEGIN
		
		select 'Gradebook -> gbCategory Area'
		
		
		---------------------------------------------------------------------------------------------------------------------------------------
		
		select 'Find gbCategory records where the associated Staff member doesn''t exist.' union select 'Should be blank.'
		select * from gbCategory 
		where iStaffID not in 
			(select iStaffID from Staff)
			
		---------------------------------------------------------------------------------------------------------------------------------------
		
		select 'Find gbCategory records where the Report Period has a different iSchoolID than the record itself, or the report period doesn''t exist.' union select 'Should be blank.'
		select * from gbCategory
		full join ReportPeriod 
			on reportperiod.iReportPeriodID = gbCategory.iReportPeriodID 
		where (reportPeriod.ischoolid != gbCategory.ischoolid)
		or gbcategory.iReportPeriodID not in 
			(select iReportPeriodID from ReportPeriod)
			
		---------------------------------------------------------------------------------------------------------------------------------------
		
		select 'Find gbCategory records where the associated gbUnit record doesn''t exist.' union select 'Should be blank.'
		select * from gbCategory 
		where iUnitiD not in 
			(select iID from gbUnit)
			
		---------------------------------------------------------------------------------------------------------------------------------------
		
		--end gbCategory
		END
		
		
		---------------------------------------------------------------------------------------------------------------------------------------
		---------------------------------------------------------------------------------------------------------------------------------------
		
		
		BEGIN
		
		select 'Gradebook -> gbTasks Area'
		
		
		---------------------------------------------------------------------------------------------------------------------------------------
		
		select 'Find gbTask records where the associated gbCategory doesn''t exist.' union select 'Should be blank.'
		select * from gbtasks 
		where iCategoryID not in 
			(select iID from gbCategory)
			
		---------------------------------------------------------------------------------------------------------------------------------------
		
		select 'Find gbTask records where the Class associated with the record doesn''t exist.' union select 'Should be blank.'
		select * from gbTasks 
		where iClassID not in 
			(select iClassID from Class)
			
		---------------------------------------------------------------------------------------------------------------------------------------
		
		select 'Find gbTask records where the Date on the Task isn''t within the Date Range for the associated Class.' union select 'Should be blank.'
		select * from gbTasks 
		join class 
			on class.iClassID = gbtasks.iClassID 
		join ClassResource 
			on ClassResource.iClassID = class.iClassID
		join ClassSchedule cs 
			on cs.iClassResourceID = ClassResource.iClassResourceID  
		where (gbtasks.dTaskDate <= class.dStartDate 
		and gbtasks.dTaskDate >= class.dEndDate 
		and gbtasks.dTaskDate != ''
		and class.dStartDate != ''
		and class.dEndDate != '') 
		or	(gbtasks.dTaskDate <= (select MIN(dstartdate) from Term where iTermID = cs.iTermID) 
			 and gbtasks.dTaskDate >= (select MAX(dEndDate) from Term where iTermID = cs.iTermID))
			 
		---------------------------------------------------------------------------------------------------------------------------------------
		
		--end gbTasks
		END
		
		
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	
	--end Gradebook
	END
	
	
	
	
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	--Start the really long stuff here.
	
	
	SET NOCOUNT ON
	
	
	
	BEGIN
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	select '---------------------------------------------------------------------------------------------------------------------------------------'
	select '---------------------------------------------------------------------------------------------------------------------------------------'
	select 'Find LookupValues with a value not in the Datatype they are associated with.'
	
	drop table #temptable --table to hold table names and column names
	drop table #temptable2 --table to hold invalid lookup values (int), actual value, and associated columns, and tables.

	create table #temptable2(
		lv_iID int,
		lv_value varchar(100),
		lv_Column varchar(100),
		lv_Table varchar(100)
	)

	select sObj.name as cTable, sCol.name as cColumn
	into #temptable 
	from sys.tables sTable
	inner join sys.columns sCol
		on sCol.object_id = sTable.object_id 
	inner join sys.objects sObj
		on sCol.object_id = sObj.object_id
	inner join sys.types sType 
		on sCol.system_type_id=sType.system_type_id and sType.is_user_defined=0 
	and (SCHEMA_NAME(sTable.schema_id)=N'dbo')
	--where sType.name = ''
	and sCol.name like 'iLV_%'
	order by sObj.name, sCol.column_id

	--select * from #temptable order by ccolumn


	declare @TableName varchar(50);
	declare @ColumnName varchar(50);
	
	
	declare @commandstring varchar(max);
	set @commandstring = ''

	declare cOuter cursor for
		select distinct cTable from #temptable


	open cOuter
	fetch cOuter into @TableName
	while @@fetch_status=0
	begin

		declare cInner cursor for 
			select cColumn from #temptable where cTable like @TableName
	   
		open cInner
		fetch cInner into @ColumnName
		while @@fetch_status=0
		begin
	   
			set @commandstring =  'insert into #temptable2 (lv_iID, lv_value, lv_column, lv_table) select ' + @ColumnName + ', dbo.GetLookupValue(' + @ColumnName + '), '''+ @ColumnName + ''', ''' + @TableName + ''' from ' + @TableName + ' where ' + @ColumnName + ' != 0 and ' + @ColumnName + ' not in (select ilookupvaluesid from LookupValues where iDataTypesID = (select iDataTypesID from fields where cFieldName = ''' + @ColumnName + ''' and cAlias = '''+ @TableName +'''))'
			exec(@commandstring)	     
			     
		fetch next from cInner into @ColumnName
		end
		close cInner
		deallocate cInner

	fetch next from cOuter into @TableName
	end
	close cOuter
	deallocate cOuter
	

	select * from #temptable2
	

	--end Lookupvalues checker
	END
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	
	
	
	
	
	
	
	SET NOCOUNT OFF
	
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------