Student Transcript Readers

DECLARE @currentSchoolYear int = (SELECT TOP(1) SchoolYear FROM ##CurrentSchoolYear$runtimeId)

;WITH [TranscriptPriorityByPerformance] AS
(
    SELECT
        ct.*
        , s.SchoolId
        , td.TermDescriptorId AS TermTypeId
        , tdd.[Description] AS TermTypeDescription
        , gld.CodeValue AS GradeLevelWhenTakenCodeValue
        , ROW_NUMBER() OVER 
        (
            PARTITION BY 
                ct.StudentUSI,
                ct.SchoolYear,
                tdd.[Description],
                ct.CourseCode,
                gld.CodeValue,
                s.SchoolId
            ORDER BY
                CASE 
                    WHEN (cadd.CodeValue = 'Pass') THEN 1 
                    WHEN (cadd.CodeValue = 'Fail') THEN 2
                    WHEN (cadd.CodeValue = 'Withdrawn') THEN 3
                    WHEN (cadd.CodeValue = 'Incomplete') THEN 4
                    ELSE 5
                END ASC,
                ct.FinalNumericGradeEarned DESC,
                CASE WHEN ct.FinalLetterGradeEarned IS NOT NULL THEN 1 ELSE 2 END ASC,
                ct.FinalLetterGradeEarned ASC
        ) AS [TranscriptPriorityByPerformanceOrderKey]
        FROM edfi.CourseTranscript ct
        LEFT JOIN edfi.Descriptor gld 
            ON gld.DescriptorId = ct.WhenTakenGradeLevelDescriptorId
        INNER JOIN edfi.CourseAttemptResultDescriptor cad
            ON cad.CourseAttemptResultDescriptorId =  ct.CourseAttemptResultDescriptorId
        INNER JOIN edfi.Descriptor cadd 
            ON cadd.DescriptorId = cad.CourseAttemptResultDescriptorId
        INNER JOIN edfi.TermDescriptor td
            ON ct.TermDescriptorId = td.TermDescriptorId
        INNER JOIN edfi.Descriptor tdd
            ON td.TermDescriptorId = tdd.DescriptorId
        INNER JOIN edfi.School s
            ON s.SchoolId = ct.CourseEducationOrganizationId
), [CourseTranscriptsWithPriorityByGradeAndSchool] AS
(
    SELECT
        t.*
        ,ROW_NUMBER() OVER 
            (
                PARTITION BY
                    t.StudentUSI,
                    t.SchoolYear,
                    t.TermTypeId,
                    t.CourseCode,
                    t.GradeLevelWhenTakenCodeValue
                ORDER BY 
                    t.TranscriptPriorityByPerformanceOrderKey ASC,
                    ssa.EntryDate DESC,
                    t.SchoolId ASC
            ) AS [PriorityKeyByGradeAndSchool]
        FROM [TranscriptPriorityByPerformance] t
        LEFT JOIN ##CurrentStudentSchoolAssociation$runtimeId ssa
            ON ssa.StudentUSI = t.StudentUSI
            AND ssa.SchoolId = t.SchoolId
            AND ssa.IsEnrolledToSchool = 1
)
SELECT
    stu.StudentUniqueId as ParentId
    ,ct.SchoolYear
    ,ct.CourseEducationOrganizationId AS SchoolId
    ,ct.EducationOrganizationId AS LocalEducationAgencyId
    ,ct.TermTypeId
    ,ct.TermTypeDescription
    ,ct.CourseCode AS IdentityCourseCode
    ,c.CourseTitle
    ,asd.AcademicSubjectDescriptorId as AcademicSubjectDescriptor
    ,ct.CourseAttemptResultDescriptorId AS CourseAttemptResultDescriptor
    ,ct.AttemptedCredits AS CreditsAttempted
    ,ct.EarnedCredits AS CreditsEarned
    ,ct.WhenTakenGradeLevelDescriptorId AS GradeLevelWhenTaken
    ,ct.FinalLetterGradeEarned AS FinalLetterGrade
    ,ct.FinalNumericGradeEarned AS FinalNumericGrade
    ,(SUBSTRING((SELECT ',' + CAST(clc.CourseLevelCharacteristicDescriptorId AS NVARCHAR(10))
                FROM edfi.CourseLevelCharacteristic clc
                WHERE clc.CourseCode = ct.CourseCode AND clc.EducationOrganizationId = CourseEducationOrganizationId
                ORDER BY clc.CourseLevelCharacteristicDescriptorId FOR XML PATH('')), 2, 2000000)) AS CourseLevelCharacteristics
    ,ct.[PriorityKeyByGradeAndSchool] AS [DashboardOrderKeyForHistoricalTranscriptsWithNoSchool]
FROM edfi.Student stu
    INNER JOIN [CourseTranscriptsWithPriorityByGradeAndSchool] ct ON
        ct.StudentUSI = stu.StudentUSI
        AND ct.[TranscriptPriorityByPerformanceOrderKey] = 1
    LEFT JOIN edfi.Course c
        ON ct.CourseEducationOrganizationId = c.EducationOrganizationId
        AND ct.CourseCode = c.CourseCode
    LEFT JOIN edfi.AcademicSubjectDescriptor asd
        ON c.AcademicSubjectDescriptorId = asd.AcademicSubjectDescriptorId
    INNER JOIN ##CurrentStudentSchoolAssociation$runtimeId sortOrder
        ON sortOrder.StudentUSI = ct.StudentUSI
        AND sortOrder.LocalEducationAgencyId = @currentLeaId
ORDER BY sortOrder.LocalEducationAgencyId, sortOrder.SchoolId, ct.StudentUSI, ct.SchoolYear, ct.TermDescriptorId, GradeLevelWhenTaken