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

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
				WHERE TABLE_NAME = 'qt_TermlyReviewToolGrade9')
BEGIN
    EXEC dbo.sp_CreateSurfaceQueryTable @SurfaceID = 3083 , -- int
                                        @UserLink = 0 ,     -- int
                                        @UserId = 219 ,		-- int
                                        @QueSetLabels = 1	-- bit
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
				WHERE TABLE_NAME = 'qt_Grade8and9CoreSubjects')
BEGIN
    EXEC dbo.sp_CreateSurfaceQueryTable @SurfaceID = 3111 , -- 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.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_FirstName  + '' '' + T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Surname [Name Surname]
        ,T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Gender [Gender]
        ,T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Race [Race]
        ,T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_School [School]
		,T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_IntakeYear [Intake Year]
        ,T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Grade [Grade]
		,S.CoreSubjects_CoreSubjects_Subjects [Subject]
        ,MAX(CASE WHEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Term = 1
				THEN CASE S.CoreSubjects_CoreSubjects_Subjects
					WHEN ''English'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_English
					WHEN ''Maths'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Maths
					WHEN ''Natural Science'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_NaturalScience
				ELSE 0 END
			END) T1Result
        ,MAX(CASE WHEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Term = 1
				THEN CASE S.CoreSubjects_CoreSubjects_Subjects
					WHEN ''English'' THEN A.SchoolAverages_Term1_Term1English
					WHEN ''Maths'' THEN A.SchoolAverages_Term1_Term1Mathematics
					WHEN ''Natural Science'' THEN A.SchoolAverages_Term1_Term1NaturalScience
				ELSE 0 END
			END) T1Average
        ,MAX(CASE WHEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Term = 2
				THEN CASE S.CoreSubjects_CoreSubjects_Subjects
					WHEN ''English'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_English
					WHEN ''Maths'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Maths
					WHEN ''Natural Science'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_NaturalScience
				ELSE 0 END
			END) T2Result
        ,MAX(CASE WHEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Term = 2
				THEN CASE S.CoreSubjects_CoreSubjects_Subjects
					WHEN ''English'' THEN A.SchoolAverages_Term2_Term2English
					WHEN ''Maths'' THEN A.SchoolAverages_Term2_Term2Mathematics
					WHEN ''Natural Science'' THEN A.SchoolAverages_Term2_Term2NaturalScience
				ELSE 0 END
			END) T2Average
        ,MAX(CASE WHEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Term = 3
				THEN CASE S.CoreSubjects_CoreSubjects_Subjects
					WHEN ''English'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_English
					WHEN ''Maths'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Maths
					WHEN ''Natural Science'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_NaturalScience
				ELSE 0 END
			END) T3Result
        ,MAX(CASE WHEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Term = 3
				THEN CASE S.CoreSubjects_CoreSubjects_Subjects
					WHEN ''English'' THEN A.SchoolAverages_Term3_Term3English
					WHEN ''Maths'' THEN A.SchoolAverages_Term3_Term3Mathematics
					WHEN ''Natural Science'' THEN A.SchoolAverages_Term3_Term3NaturalScience
				ELSE 0 END
			END) T3Average
        ,MAX(CASE WHEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Term = 4
				THEN CASE S.CoreSubjects_CoreSubjects_Subjects
					WHEN ''English'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_English
					WHEN ''Maths'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Maths
					WHEN ''Natural Science'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_NaturalScience
				ELSE 0 END
			END) T4Result
        ,MAX(CASE WHEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Term = 4
				THEN CASE S.CoreSubjects_CoreSubjects_Subjects
					WHEN ''English'' THEN A.SchoolAverages_Term4_Term4English
					WHEN ''Maths'' THEN A.SchoolAverages_Term4_Term4Mathematics
					WHEN ''Natural Science'' THEN A.SchoolAverages_Term4_Term4NaturalScience
				ELSE 0 END
			END) T4Average
		,CASE WHEN MAX(CASE WHEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Term = 1
				THEN CASE S.CoreSubjects_CoreSubjects_Subjects
					WHEN ''English'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_English
					WHEN ''Maths'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Maths
					WHEN ''Natural Science'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_NaturalScience
				ELSE 0 END
				END) >=
				MAX(CASE WHEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Term = 1
					THEN CASE S.CoreSubjects_CoreSubjects_Subjects
						WHEN ''English'' THEN A.SchoolAverages_Term1_Term1English
						WHEN ''Maths'' THEN A.SchoolAverages_Term1_Term1Mathematics
						WHEN ''Natural Science'' THEN A.SchoolAverages_Term1_Term1NaturalScience
					ELSE 0 END
				END)
			THEN 1
			ELSE 0 END Term1
		,CASE WHEN MAX(CASE WHEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Term = 2
				THEN CASE S.CoreSubjects_CoreSubjects_Subjects
					WHEN ''English'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_English
					WHEN ''Maths'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Maths
					WHEN ''Natural Science'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_NaturalScience
				ELSE 0 END
				END) >=
				MAX(CASE WHEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Term = 2
					THEN CASE S.CoreSubjects_CoreSubjects_Subjects
						WHEN ''English'' THEN A.SchoolAverages_Term2_Term2English
						WHEN ''Maths'' THEN A.SchoolAverages_Term2_Term2Mathematics
						WHEN ''Natural Science'' THEN A.SchoolAverages_Term2_Term2NaturalScience
					ELSE 0 END
				END)
			THEN 1
			ELSE 0 END Term2
		,CASE WHEN MAX(CASE WHEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Term = 3
				THEN CASE S.CoreSubjects_CoreSubjects_Subjects
					WHEN ''English'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_English
					WHEN ''Maths'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Maths
					WHEN ''Natural Science'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_NaturalScience
				ELSE 0 END
				END) >=
				MAX(CASE WHEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Term = 3
					THEN CASE S.CoreSubjects_CoreSubjects_Subjects
						WHEN ''English'' THEN A.SchoolAverages_Term3_Term3English
						WHEN ''Maths'' THEN A.SchoolAverages_Term3_Term3Mathematics
						WHEN ''Natural Science'' THEN A.SchoolAverages_Term3_Term3NaturalScience
					ELSE 0 END
				END)
			THEN 1
			ELSE 0 END Term3
		,CASE WHEN MAX(CASE WHEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Term = 4
				THEN CASE S.CoreSubjects_CoreSubjects_Subjects
					WHEN ''English'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_English
					WHEN ''Maths'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Maths
					WHEN ''Natural Science'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_NaturalScience
				ELSE 0 END
				END) >=
				MAX(CASE WHEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Term = 4
					THEN CASE S.CoreSubjects_CoreSubjects_Subjects
						WHEN ''English'' THEN A.SchoolAverages_Term4_Term4English
						WHEN ''Maths'' THEN A.SchoolAverages_Term4_Term4Mathematics
						WHEN ''Natural Science'' THEN A.SchoolAverages_Term4_Term4NaturalScience
					ELSE 0 END
				END)
			THEN 1
			ELSE 0 END Term4
		,CASE WHEN MAX(CASE WHEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Term = 1
				THEN CASE S.CoreSubjects_CoreSubjects_Subjects
					WHEN ''English'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_English
					WHEN ''Maths'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Maths
					WHEN ''Natural Science'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_NaturalScience
				ELSE 0 END
				END) >=
				MAX(CASE WHEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Term = 1
					THEN CASE S.CoreSubjects_CoreSubjects_Subjects
						WHEN ''English'' THEN A.SchoolAverages_Term1_Term1English
						WHEN ''Maths'' THEN A.SchoolAverages_Term1_Term1Mathematics
						WHEN ''Natural Science'' THEN A.SchoolAverages_Term1_Term1NaturalScience
					ELSE 0 END
				END)
			THEN 1
			ELSE 0 END +
		CASE WHEN MAX(CASE WHEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Term = 2
				THEN CASE S.CoreSubjects_CoreSubjects_Subjects
					WHEN ''English'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_English
					WHEN ''Maths'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Maths
					WHEN ''Natural Science'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_NaturalScience
				ELSE 0 END
				END) >=
				MAX(CASE WHEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Term = 2
					THEN CASE S.CoreSubjects_CoreSubjects_Subjects
						WHEN ''English'' THEN A.SchoolAverages_Term2_Term2English
						WHEN ''Maths'' THEN A.SchoolAverages_Term2_Term2Mathematics
						WHEN ''Natural Science'' THEN A.SchoolAverages_Term2_Term2NaturalScience
					ELSE 0 END
				END)
			THEN 1
			ELSE 0 END +
		CASE WHEN MAX(CASE WHEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Term = 3
				THEN CASE S.CoreSubjects_CoreSubjects_Subjects
					WHEN ''English'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_English
					WHEN ''Maths'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Maths
					WHEN ''Natural Science'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_NaturalScience
				ELSE 0 END
				END) >=
				MAX(CASE WHEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Term = 3
					THEN CASE S.CoreSubjects_CoreSubjects_Subjects
						WHEN ''English'' THEN A.SchoolAverages_Term3_Term3English
						WHEN ''Maths'' THEN A.SchoolAverages_Term3_Term3Mathematics
						WHEN ''Natural Science'' THEN A.SchoolAverages_Term3_Term3NaturalScience
					ELSE 0 END
				END)
			THEN 1
			ELSE 0 END +
		CASE WHEN MAX(CASE WHEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Term = 4
				THEN CASE S.CoreSubjects_CoreSubjects_Subjects
					WHEN ''English'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_English
					WHEN ''Maths'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Maths
					WHEN ''Natural Science'' THEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_NaturalScience
				ELSE 0 END
				END) >=
				MAX(CASE WHEN T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Term = 4
					THEN CASE S.CoreSubjects_CoreSubjects_Subjects
						WHEN ''English'' THEN A.SchoolAverages_Term4_Term4English
						WHEN ''Maths'' THEN A.SchoolAverages_Term4_Term4Mathematics
						WHEN ''Natural Science'' THEN A.SchoolAverages_Term4_Term4NaturalScience
					ELSE 0 END
				END)
			THEN 1
			ELSE 0 END Achievement
FROM	dbo.qt_TermlyReviewToolGrade9 T
		JOIN dbo.qt_Grade8and9CoreSubjects S
			ON S.isActive = 1
		LEFT JOIN dbo.qt_Averages A
			ON REPLACE(A.SchoolAverages_SchoolAverages_Grade,''Grade '','''') = T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Grade
			AND A.SchoolAverages_SchoolAverages_IntakeYear = T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_IntakeYear
			AND A.SchoolAverages_SchoolAverages_School = T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_School
'

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

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

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

IF(@intakeYearFilter <> '')
BEGIN
	SET @whereYear = ' T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_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.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_FirstName 
        ,T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Surname 
        ,T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Gender 
        ,T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Race 
        ,T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_School 
		,T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_IntakeYear
        ,T.ResultsIntakeToolGrade9Tab_ResultsIntakeToolGrade9Group_Grade 
		,S.CoreSubjects_CoreSubjects_Subjects
'

EXEC (@query)


