USP_DATALIST_COURSEGRADING

CREATE procedure dbo.USP_DATALIST_COURSEGRADING(@CONTEXTID uniqueidentifier)
as
    set nocount on;

    select COURSEGRADING.ID,
           ACADEMICYEARNAMECODE.DESCRIPTION 'ACADEMICYEARNAME',
           SESSIONNAMECODE.DESCRIPTION 'SESSIONNAME',
           (select stuff((
                select '; ' + MARKINGCOLUMN.DISPLAYNAME
                from
                    dbo.COURSEGRADINGMARKINGCOLUMN cgmc
                join dbo.MARKINGCOLUMN on cgmc.MARKINGCOLUMNID = MARKINGCOLUMN.ID
                join dbo.MARKINGCOLUMNDESCRIPTIONCODE on MARKINGCOLUMN.MARKINGCOLUMNDESCRIPTIONCODEID = MARKINGCOLUMNDESCRIPTIONCODE.ID
                where
                    cgmc.GRADED = 1 and
                    cgmc.COURSEGRADINGID = COURSEGRADING.ID
                order by MARKINGCOLUMN.sequence
                for xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, '')) as 'GRADEDMC',                   
            dbo.UDA_BUILDLIST(DISTINCT (TRANSLATIONTABLE.NAME)) 'TRANSLATIONTABLES',
            case when count(dbo.STUDENTMARKINGCOLUMNGRADE.ID) > 0 then 1 else 0 end 'HASGRADE'
    from dbo.V_COURSEGRADING as COURSEGRADING
    join dbo.COURSE on COURSEGRADING.COURSEID = COURSE.ID
    join dbo.SESSION on dbo.UFN_SESSION_GET_FORSCHOOL_BYDATE(COURSE.SCHOOLID, COURSEGRADING.STARTDATE, COURSEGRADING.ENDDATE) = SESSION.ID
    join dbo.SESSIONNAMECODE on SESSION.SESSIONNAMECODEID = SESSIONNAMECODE.ID
    join dbo.ACADEMICYEAR on SESSION.ACADEMICYEARID = ACADEMICYEAR.ID
    join dbo.ACADEMICYEARNAMECODE on ACADEMICYEAR.ACADEMICYEARNAMECODEID = ACADEMICYEARNAMECODE.ID
    left join dbo.COURSEGRADINGMARKINGCOLUMN on COURSEGRADING.ID = COURSEGRADINGMARKINGCOLUMN.COURSEGRADINGID
    left join dbo.TRANSLATIONTABLE on COURSEGRADINGMARKINGCOLUMN.TRANSLATIONTABLEID = TRANSLATIONTABLE.ID
    left join dbo.STUDENTMARKINGCOLUMNGRADE on (COURSEGRADINGMARKINGCOLUMN.ID = STUDENTMARKINGCOLUMNGRADE.COURSEGRADINGMARKINGCOLUMNID) and
            STUDENTMARKINGCOLUMNGRADE.GRADEISBLANK = 0
    where COURSEGRADING.COURSEID = @CONTEXTID
    group by COURSEGRADING.ID, ACADEMICYEARNAMECODE.DESCRIPTION, SESSIONNAMECODE.DESCRIPTION, COURSEGRADING.STARTDATE
    order by COURSEGRADING.STARTDATE