UFN_MARKINGCOLUMN_GETGRADEDTERMS

CREATE function dbo.UFN_MARKINGCOLUMN_GETGRADEDTERMS
(
    @MARKINGCOLUMNID uniqueidentifier = null
)
returns table
as return (

        select 
            ENROLLEDTERM.TERMNAMECODEID, 
            MARKINGCOLUMNTERM.ID 'MARKINGCOLUMNTERMID', 
            case when min(case when (OTHER_ENROLLMENTS.ID is null) then 0 else 1 end) > 0 then 1 else 0 end 'ENROLLEDINOTHERTERMS',                                    
            case when min(case when (OTHER_ENROLLMENTS.ID is null) then 0 else 1 end) > 0 then dbo.UDA_BUILDLIST(OTHER_ENROLLEDTERMS.TERMNAMECODEID) else '' end 'OTHERENROLLEDTERMS'  
        from dbo.STUDENTMARKINGCOLUMNGRADE
        inner join dbo.COURSEGRADINGMARKINGCOLUMN 
            on STUDENTMARKINGCOLUMNGRADE.COURSEGRADINGMARKINGCOLUMNID = COURSEGRADINGMARKINGCOLUMN.ID
        inner join dbo.COURSEGRADING 
            on COURSEGRADINGMARKINGCOLUMN.COURSEGRADINGID = COURSEGRADING.ID
        inner join dbo.COURSE 
            on COURSEGRADING.COURSEID = COURSE.ID        
        inner join dbo.STUDENTCLASSMEETINGGROUP 
            on STUDENTMARKINGCOLUMNGRADE.STUDENTCLASSMEETINGGROUPID = STUDENTCLASSMEETINGGROUP.ID
        inner join dbo.STUDENTCOURSE 
            on STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID = STUDENTCOURSE.ID
        left outer join dbo.STUDENTCLASSMEETINGGROUP ENROLLMENTS 
            on STUDENTCOURSE.ID = ENROLLMENTS.STUDENTCOURSEID
            --we want to allow the same studentClassMeetingGroup as the student grade so the term not on the student grade can be verified

            --and ENROLLMENTS.ID <> STUDENTCLASSMEETINGGROUP.ID 

        --make sure this term is enrolled (even if it's not the one on the student grade).            

        left outer join dbo.CLASSMEETINGGROUP ENROLLED_CLASSMEETINGGROUP
            on ENROLLMENTS.CLASSMEETINGGROUPID = ENROLLED_CLASSMEETINGGROUP.ID
        left outer join dbo.TERM ENROLLEDTERM 
            on ENROLLEDTERM.ID = dbo.UFN_TERM_GET_FORSCHOOL_BYDATE(COURSE.SCHOOLID, ENROLLED_CLASSMEETINGGROUP.STARTDATE, ENROLLED_CLASSMEETINGGROUP.ENDDATE)                            
        left outer join dbo.MARKINGCOLUMNTERM 
            on ENROLLEDTERM.TERMNAMECODEID = MARKINGCOLUMNTERM.TERMNAMECODEID and
                COURSEGRADINGMARKINGCOLUMN.MARKINGCOLUMNID = MARKINGCOLUMNTERM.MARKINGCOLUMNID                
        --join to check there are other enrollments other than 'this' term.

        left outer join dbo.STUDENTCLASSMEETINGGROUP OTHER_ENROLLMENTS 
            on STUDENTCOURSE.ID = OTHER_ENROLLMENTS.STUDENTCOURSEID and 
                OTHER_ENROLLMENTS.ID <> STUDENTCLASSMEETINGGROUP.ID
        left outer join dbo.STUDENTCLASSMEETINGGROUP OTHER_ENROLLMENTS_WITHDUPLICATES 
            on STUDENTCOURSE.ID = OTHER_ENROLLMENTS_WITHDUPLICATES.STUDENTCOURSEID
            --we want to allow the same studentClassMeetingGroup as the student grade so the term not on the student grade will get the corresponding "other term(s)"

            --and OTHER_ENROLLMENTS.ID <> STUDENTCLASSMEETINGGROUP.ID 

        left outer join dbo.CLASSMEETINGGROUP OTHER_CLASSMEETINGGROUP
            on OTHER_ENROLLMENTS_WITHDUPLICATES.CLASSMEETINGGROUPID = OTHER_CLASSMEETINGGROUP.ID and 
                (ENROLLEDTERM.STARTDATE <> OTHER_CLASSMEETINGGROUP.STARTDATE or ENROLLEDTERM.ENDDATE <> OTHER_CLASSMEETINGGROUP.ENDDATE)
        left outer join dbo.TERM OTHER_ENROLLEDTERMS 
            on ENROLLEDTERM.ID <> OTHER_ENROLLEDTERMS.ID and 
                OTHER_ENROLLEDTERMS.ID = dbo.UFN_TERM_GET_FORSCHOOL_BYDATE(COURSE.SCHOOLID, OTHER_CLASSMEETINGGROUP.STARTDATE, OTHER_CLASSMEETINGGROUP.ENDDATE)                
        where ((@MARKINGCOLUMNID is null) or (COURSEGRADINGMARKINGCOLUMN.MARKINGCOLUMNID = @MARKINGCOLUMNID)) and
            STUDENTMARKINGCOLUMNGRADE.GRADEISBLANK = 0
        group by ENROLLEDTERM.TERMNAMECODEID, MARKINGCOLUMNTERM.ID           
)