CREATE VIEW vOG_1920_IY1_InYearGradesFromProMonitor
AS

/*
This Example View will take unique InYear data from ProMonitor 
for any enrolment in any student group where grades have been entered against Assessment IY1 in Unit IY in 19/20
*/

SELECT TT.AcademicYearID, TT.StudentID, TT.CourseCode, ProMonitor.dbo.EnrolmentAssessment.Grade
FROM
	(
	--We don't want duplicated data.  If there are duplicates get the MIN ID
	SELECT Min(T.EnrolmentAssessmentID) AS MinEnrolmentAssessmentID, T.AcademicYearID, T.StudentID, T.CourseCode
	FROM
		(
		--Get the IY data we need
		SELECT
		ProMonitor.dbo.Enrolment.AcademicYearID,
		ProMonitor.dbo.Enrolment.StudentID,
		ProMonitor.dbo.Course.CourseCode,
		ProMonitor.dbo.EnrolmentAssessment.EnrolmentAssessmentID

		FROM
		ProMonitor.dbo.EnrolmentAssessment WITH (NOLOCK)
			INNER JOIN ProMonitor.dbo.Enrolment WITH (NOLOCK)
			ON ProMonitor.dbo.EnrolmentAssessment.PMEnrolmentID = ProMonitor.dbo.Enrolment.PMEnrolmentID
				INNER JOIN ProMonitor.dbo.Assessment WITH (NOLOCK)
				ON ProMonitor.dbo.Assessment.AssessmentID = ProMonitor.dbo.EnrolmentAssessment.AssessmentID
					INNER JOIN ProMonitor.dbo.Course WITH (NOLOCK)
					ON ProMonitor.dbo.Course.CourseID = ProMonitor.dbo.Enrolment.CourseID
						INNER JOIN ProMonitor.dbo.Unit WITH (NOLOCK)
						ON ProMonitor.dbo.Unit.UnitID = ProMonitor.dbo.Assessment.UnitID
		WHERE 
		ProMonitor.dbo.Enrolment.AcademicYearID = '19/20'					--AcademicYear
		AND ProMonitor.dbo.Unit.UnitCode = 'IY'								--InYear Unit
		AND ProMonitor.dbo.Assessment.AssessmentCode = 'IY1'				--InYear Assessment
		AND LEN(ISNULL(ProMonitor.dbo.EnrolmentAssessment.Grade,'')) > 0	--Only where there are grades
		) T

	GROUP BY T.AcademicYearID, T.StudentID, T.CourseCode
	) TT

INNER JOIN ProMonitor.dbo.EnrolmentAssessment WITH (NOLOCK)
ON ProMonitor.dbo.EnrolmentAssessment.EnrolmentAssessmentID = TT.MinEnrolmentAssessmentID