CREATE VIEW vOG_1920_InYear_Point1 AS /* first section is for voc courses using ProjectedOverallGradeFromUnits */ SELECT academicyearID ,StudentID AS StudentRef ,RTRIM(CourseCode) AS CourseCode ,CASE WHEN ProjectedOverallGradeFromUnits IS NOT NULL THEN ProjectedOverallGradeFromUnits WHEN ProjectedOverallGradeFromUnits IS NULL AND NOT CountOfCompletedAssessments IS NULL THEN 'A'+RTRIM(CAST([CountOfCompletedAssessments]AS char))+'/'+RTRIM(CAST([CountOfTotalAssessments] as char))+' T'+ RTRIM(CAST([CountOfCompletedTasks]AS char))+'/'+RTRIM(CAST([CountOfTotalTasks] as char)) ELSE 'PM Error' END AS Grade FROM ProMonitor.[dbo].[vReports_Markbook] WHERE academicyearID = '19/20' /* this is where you choose only voc enrolments. Here it's done using StudentGroupType but you could also use course code, course title etc: */ AND StudentGroupTypeID NOT IN (256,257,258,259,260,261,262,263,309,310, 269, 270, 271, 264, 265, 69) /* second section is for A level courses or any other course types using ProjectedOverallGradeFromAssessments */ UNION SELECT academicyearID ,StudentID AS StudentRef ,RTRIM(CourseCode) AS CourseCode ,CASE WHEN ProjectedOverallGradeFromAssessments IS NOT NULL THEN /*this assumes borderline grades are used and picks the lower of the two: */ CASE WHEN ProjectedOverallGradeFromAssessments = 'A*/A' THEN 'A' WHEN ProjectedOverallGradeFromAssessments = 'A/B' THEN 'B' WHEN ProjectedOverallGradeFromAssessments = 'B/C' THEN 'C' WHEN ProjectedOverallGradeFromAssessments = 'C/D' THEN 'D' WHEN ProjectedOverallGradeFromAssessments = 'D/E' THEN 'E' WHEN ProjectedOverallGradeFromAssessments = 'E/U' THEN 'U' ELSE ProjectedOverallGradeFromAssessments END ELSE 'PM Error' END AS Grade FROM ProMonitor.[dbo].[vReports_Markbook] WHERE academicyearID = '19/20' /* this is where you choose only A level enrolments. Here it's done using StudentGroupType but you could also use course code, course title etc: */ AND StudentGroupTypeID IN (256,257,258,259,260,261,262,263,309,310)