CREATE PROC [dbo].[sp_STUD1_GetReportTermlyGrade10Average]
	@schoolFilter VARCHAR(500)
	,@subjectFilter VARCHAR(500)
	,@intakeYearFilter VARCHAR(500)
AS

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
				WHERE TABLE_NAME = 'qt_TermlyReviewToolGrade10')
BEGIN
    EXEC dbo.sp_CreateSurfaceQueryTable @SurfaceID = 3047 , -- int
                                        @UserLink = 0 ,     -- int
                                        @UserId = 219 ,		-- int
                                        @QueSetLabels = 1	-- bit
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
				WHERE TABLE_NAME = 'qt_Grade10to12CoreSubjects')
BEGIN
    EXEC dbo.sp_CreateSurfaceQueryTable @SurfaceID = 3113 , -- int
                                        @UserLink = 0 ,     -- int
                                        @UserId = 219 ,		-- int
                                        @QueSetLabels = 1	-- bit
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
				WHERE TABLE_NAME = 'qt_Averages')
BEGIN
    EXEC dbo.sp_CreateSurfaceQueryTable @SurfaceID = 3097 , -- int
                                        @UserLink = 0 ,     -- int
                                        @UserId = 219 ,		-- int
                                        @QueSetLabels = 1	-- bit
END

DECLARE @query VARCHAR(MAX)

SET @query = N'
SELECT	T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_FirstName  + '' '' + T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Surname [Name Surname]
        ,T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Gender [Gender]
        ,T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Race [Race]
        ,T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_School [School]
		,T.ResultsIntakeToolGrade1012_ResultsIntakeToolGrade1012_IntakeYear [Intake Year]
        ,T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Grade [Grade]
		,S.CoreSubjects_CoreSubjects_Subject [Subject]
        ,MAX(CASE WHEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Term = 1
				THEN CASE S.CoreSubjects_CoreSubjects_Subject
					WHEN ''English'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_English
					WHEN ''Maths'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Maths
					WHEN ''Maths Literacy'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_MathsLiteracy
					WHEN ''Physical Science'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_PhysicalScience
				ELSE 0 END
			END) T1Result
        ,MAX(CASE WHEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Term = 1
				THEN CASE S.CoreSubjects_CoreSubjects_Subject
					WHEN ''English'' THEN A.SchoolAverages_Term1_Term1English
					WHEN ''Maths'' THEN A.SchoolAverages_Term1_Term1Mathematics
					WHEN ''Maths Literacy'' THEN A.SchoolAverages_Term1_Term1MathematicsLiteracy
					WHEN ''Physical Science'' THEN A.SchoolAverages_Term1_Term1PhysicalScience
				ELSE 0 END
			END) T1Average
        ,MAX(CASE WHEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Term = 2
				THEN CASE S.CoreSubjects_CoreSubjects_Subject
					WHEN ''English'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_English
					WHEN ''Maths'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Maths
					WHEN ''Maths Literacy'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_MathsLiteracy
					WHEN ''Physical Science'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_PhysicalScience
				ELSE 0 END
			END) T2Result
        ,MAX(CASE WHEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Term = 2
				THEN CASE S.CoreSubjects_CoreSubjects_Subject
					WHEN ''English'' THEN A.SchoolAverages_Term2_Term2English
					WHEN ''Maths'' THEN A.SchoolAverages_Term2_Term2Mathematics
					WHEN ''Maths Literacy'' THEN A.SchoolAverages_Term2_Term2MathematicsLiteracy
					WHEN ''Physical Science'' THEN A.SchoolAverages_Term2_Term2PhysicalScience
				ELSE 0 END
			END) T2Average
        ,MAX(CASE WHEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Term = 3
				THEN CASE S.CoreSubjects_CoreSubjects_Subject
					WHEN ''English'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_English
					WHEN ''Maths'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Maths
					WHEN ''Maths Literacy'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_MathsLiteracy
					WHEN ''Physical Science'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_PhysicalScience
				ELSE 0 END
			END) T3Result
        ,MAX(CASE WHEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Term = 3
				THEN CASE S.CoreSubjects_CoreSubjects_Subject
					WHEN ''English'' THEN A.SchoolAverages_Term3_Term3English
					WHEN ''Maths'' THEN A.SchoolAverages_Term3_Term3Mathematics
					WHEN ''Maths Literacy'' THEN A.SchoolAverages_Term3_Term3MathematicsLiteracy
					WHEN ''Physical Science'' THEN A.SchoolAverages_Term3_Term3PhysicalScience
				ELSE 0 END
			END) T3Average
        ,MAX(CASE WHEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Term = 4
				THEN CASE S.CoreSubjects_CoreSubjects_Subject
					WHEN ''English'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_English
					WHEN ''Maths'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Maths
					WHEN ''Maths Literacy'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_MathsLiteracy
					WHEN ''Physical Science'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_PhysicalScience
				ELSE 0 END
			END) T4Result
        ,MAX(CASE WHEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Term = 4
				THEN CASE S.CoreSubjects_CoreSubjects_Subject
					WHEN ''English'' THEN A.SchoolAverages_Term4_Term4English
					WHEN ''Maths'' THEN A.SchoolAverages_Term4_Term4Mathematics
					WHEN ''Maths Literacy'' THEN A.SchoolAverages_Term4_Term4MathematicsLiteracy
					WHEN ''Physical Science'' THEN A.SchoolAverages_Term4_Term4PhysicalScience
				ELSE 0 END
			END) T4Average
		,CASE WHEN MAX(CASE WHEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Term = 1
				THEN CASE S.CoreSubjects_CoreSubjects_Subject
					WHEN ''English'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_English
					WHEN ''Maths'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Maths
					WHEN ''Maths Literacy'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_MathsLiteracy
					WHEN ''Physical Science'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_PhysicalScience
				ELSE 0 END
				END) >=
				MAX(CASE WHEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Term = 1
					THEN CASE S.CoreSubjects_CoreSubjects_Subject
						WHEN ''English'' THEN A.SchoolAverages_Term1_Term1English
						WHEN ''Maths'' THEN A.SchoolAverages_Term1_Term1Mathematics
						WHEN ''Maths Literacy'' THEN A.SchoolAverages_Term1_Term1MathematicsLiteracy
						WHEN ''Physical Science'' THEN A.SchoolAverages_Term1_Term1PhysicalScience
					ELSE 0 END
				END)
			THEN 1
			ELSE 0 END Term1
		,CASE WHEN MAX(CASE WHEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Term = 2
				THEN CASE S.CoreSubjects_CoreSubjects_Subject
					WHEN ''English'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_English
					WHEN ''Maths'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Maths
					WHEN ''Maths Literacy'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_MathsLiteracy
					WHEN ''Physical Science'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_PhysicalScience
				ELSE 0 END
				END) >=
				MAX(CASE WHEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Term = 2
					THEN CASE S.CoreSubjects_CoreSubjects_Subject
						WHEN ''English'' THEN A.SchoolAverages_Term2_Term2English
						WHEN ''Maths'' THEN A.SchoolAverages_Term2_Term2Mathematics
						WHEN ''Maths Literacy'' THEN A.SchoolAverages_Term2_Term2MathematicsLiteracy
						WHEN ''Physical Science'' THEN A.SchoolAverages_Term2_Term2PhysicalScience
					ELSE 0 END
				END)
			THEN 1
			ELSE 0 END Term2
		,CASE WHEN MAX(CASE WHEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Term = 3
				THEN CASE S.CoreSubjects_CoreSubjects_Subject
					WHEN ''English'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_English
					WHEN ''Maths'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Maths
					WHEN ''Maths Literacy'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_MathsLiteracy
					WHEN ''Physical Science'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_PhysicalScience
				ELSE 0 END
				END) >=
				MAX(CASE WHEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Term = 3
					THEN CASE S.CoreSubjects_CoreSubjects_Subject
						WHEN ''English'' THEN A.SchoolAverages_Term3_Term3English
						WHEN ''Maths'' THEN A.SchoolAverages_Term3_Term3Mathematics
						WHEN ''Maths Literacy'' THEN A.SchoolAverages_Term3_Term3MathematicsLiteracy
						WHEN ''Physical Science'' THEN A.SchoolAverages_Term3_Term3PhysicalScience
					ELSE 0 END
				END)
			THEN 1
			ELSE 0 END Term3
		,CASE WHEN MAX(CASE WHEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Term = 4
				THEN CASE S.CoreSubjects_CoreSubjects_Subject
					WHEN ''English'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_English
					WHEN ''Maths'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Maths
					WHEN ''Maths Literacy'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_MathsLiteracy
					WHEN ''Physical Science'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_PhysicalScience
				ELSE 0 END
				END) >=
				MAX(CASE WHEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Term = 4
					THEN CASE S.CoreSubjects_CoreSubjects_Subject
						WHEN ''English'' THEN A.SchoolAverages_Term4_Term4English
						WHEN ''Maths'' THEN A.SchoolAverages_Term4_Term4Mathematics
						WHEN ''Maths Literacy'' THEN A.SchoolAverages_Term4_Term4MathematicsLiteracy
						WHEN ''Physical Science'' THEN A.SchoolAverages_Term4_Term4PhysicalScience
					ELSE 0 END
				END)
			THEN 1
			ELSE 0 END Term4
		,CASE WHEN MAX(CASE WHEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Term = 1
				THEN CASE S.CoreSubjects_CoreSubjects_Subject
					WHEN ''English'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_English
					WHEN ''Maths'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Maths
					WHEN ''Maths Literacy'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_MathsLiteracy
					WHEN ''Physical Science'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_PhysicalScience
				ELSE 0 END
				END) >=
				MAX(CASE WHEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Term = 1
					THEN CASE S.CoreSubjects_CoreSubjects_Subject
						WHEN ''English'' THEN A.SchoolAverages_Term1_Term1English
						WHEN ''Maths'' THEN A.SchoolAverages_Term1_Term1Mathematics
						WHEN ''Maths Literacy'' THEN A.SchoolAverages_Term1_Term1MathematicsLiteracy
						WHEN ''Physical Science'' THEN A.SchoolAverages_Term1_Term1PhysicalScience
					ELSE 0 END
				END)
			THEN 1
			ELSE 0 END +
		CASE WHEN MAX(CASE WHEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Term = 2
				THEN CASE S.CoreSubjects_CoreSubjects_Subject
					WHEN ''English'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_English
					WHEN ''Maths'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Maths
					WHEN ''Maths Literacy'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_MathsLiteracy
					WHEN ''Physical Science'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_PhysicalScience
				ELSE 0 END
				END) >=
				MAX(CASE WHEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Term = 2
					THEN CASE S.CoreSubjects_CoreSubjects_Subject
						WHEN ''English'' THEN A.SchoolAverages_Term2_Term2English
						WHEN ''Maths'' THEN A.SchoolAverages_Term2_Term2Mathematics
						WHEN ''Maths Literacy'' THEN A.SchoolAverages_Term2_Term2MathematicsLiteracy
						WHEN ''Physical Science'' THEN A.SchoolAverages_Term2_Term2PhysicalScience
					ELSE 0 END
				END)
			THEN 1
			ELSE 0 END +
		CASE WHEN MAX(CASE WHEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Term = 3
				THEN CASE S.CoreSubjects_CoreSubjects_Subject
					WHEN ''English'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_English
					WHEN ''Maths'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Maths
					WHEN ''Maths Literacy'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_MathsLiteracy
					WHEN ''Physical Science'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_PhysicalScience
				ELSE 0 END
				END) >=
				MAX(CASE WHEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Term = 3
					THEN CASE S.CoreSubjects_CoreSubjects_Subject
						WHEN ''English'' THEN A.SchoolAverages_Term3_Term3English
						WHEN ''Maths'' THEN A.SchoolAverages_Term3_Term3Mathematics
						WHEN ''Maths Literacy'' THEN A.SchoolAverages_Term3_Term3MathematicsLiteracy
						WHEN ''Physical Science'' THEN A.SchoolAverages_Term3_Term3PhysicalScience
					ELSE 0 END
				END)
			THEN 1
			ELSE 0 END +
		CASE WHEN MAX(CASE WHEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Term = 4
				THEN CASE S.CoreSubjects_CoreSubjects_Subject
					WHEN ''English'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_English
					WHEN ''Maths'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Maths
					WHEN ''Maths Literacy'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_MathsLiteracy
					WHEN ''Physical Science'' THEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_PhysicalScience
				ELSE 0 END
				END) >=
				MAX(CASE WHEN T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Term = 4
					THEN CASE S.CoreSubjects_CoreSubjects_Subject
						WHEN ''English'' THEN A.SchoolAverages_Term4_Term4English
						WHEN ''Maths'' THEN A.SchoolAverages_Term4_Term4Mathematics
						WHEN ''Maths Literacy'' THEN A.SchoolAverages_Term4_Term4MathematicsLiteracy
						WHEN ''Physical Science'' THEN A.SchoolAverages_Term4_Term4PhysicalScience
					ELSE 0 END
				END)
			THEN 1
			ELSE 0 END Achievement
FROM	dbo.qt_TermlyReviewToolGrade10 T
		JOIN dbo.qt_Grade10to12CoreSubjects S
			ON S.isActive = 1
		LEFT JOIN dbo.qt_Averages A
			ON REPLACE(A.SchoolAverages_SchoolAverages_Grade,''Grade '','''') = T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Grade
			AND A.SchoolAverages_SchoolAverages_IntakeYear = T.ResultsIntakeToolGrade1012_ResultsIntakeToolGrade1012_IntakeYear
			AND A.SchoolAverages_SchoolAverages_School = T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_School
'

--build filter
DECLARE @whereQuery VARCHAR(MAX) = ''
		,@whereSchool VARCHAR(500) = ''
		,@whereSubject VARCHAR(500) = ''
		,@whereYear VARCHAR(500) = ''

IF(@schoolFilter <> '')
BEGIN
	SET @whereSchool = ' T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_School IN ('+@schoolFilter+') '
	SET @whereQuery = 'WHERE' + @whereSchool;
END

IF(@subjectFilter <> '')
BEGIN
	SET @whereSubject = ' S.CoreSubjects_CoreSubjects_Subject IN ('+@subjectFilter+') '
	IF(@whereQuery='')
		SET @whereQuery = 'WHERE' + @whereSubject;
	ELSE
		SET @whereQuery = @whereQuery + 'AND' + @whereSubject;
END

IF(@intakeYearFilter <> '')
BEGIN
	SET @whereYear = ' T.ResultsIntakeToolGrade1012_ResultsIntakeToolGrade1012_IntakeYear IN ('+@intakeYearFilter+') '
	IF(@whereQuery='')
		SET @whereQuery = 'WHERE' + @whereYear;
	ELSE
		SET @whereQuery = @whereQuery + 'AND' + @whereYear;
END

SET @query = @query + @whereQuery;

SET @query = @query +'
GROUP BY T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_FirstName 
        ,T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Surname 
        ,T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Gender 
        ,T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Race 
        ,T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_School 
		,T.ResultsIntakeToolGrade1012_ResultsIntakeToolGrade1012_IntakeYear
        ,T.ResultsIntakeToolGrade10_ResultsIntakeToolGrade10Group_Grade 
		,S.CoreSubjects_CoreSubjects_Subject
'

EXEC (@query)


