UFN_STUDENTMARKINGCOLUMN_GETCLASSGRADES

CREATE function dbo.UFN_STUDENTMARKINGCOLUMN_GETCLASSGRADES
(
    @STUDENTCOURSEID uniqueidentifier,
    @STATUSCODE tinyint
)
returns @GRADESTABLE table (
    ID uniqueidentifier null,
    MCDISPLAYNAME nvarchar(10) not null,
    TRANSLATIONTABLEID uniqueidentifier null,
    VALUESALLOWEDCODE tinyint not null,
    GRADETYPECODE tinyint not null,
    NUMERICGRADE numeric(14, 2) null,
    TRANSLATIONTABLEGRADEID uniqueidentifier null,
    STUDENTCLASSMEETINGGROUPID uniqueidentifier not null,
    COURSEGRADINGMARKINGCOLUMNID uniqueidentifier not null,
    LOWESTSCOREALLOWED numeric(14,2) null,
    HIGHESTSCOREALLOWED numeric(14,2) null,
    MCSEQUENCE int not null,
    GRADEISBLANK bit not null,
  SECTION nvarchar(10) null
)
as begin
    insert into @GRADESTABLE
    select
        ID,
        MCDISPLAYNAME,
        TRANSLATIONTABLEID,
        VALUESALLOWEDCODE,
        GRADETYPECODE,
        NUMERICGRADE,
        TRANSLATIONTABLEGRADEID,
        STUDENTCLASSMEETINGGROUPID,
        COURSEGRADINGMARKINGCOLUMNID,
        LOWESTSCOREALLOWED,
        HIGHESTSCOREALLOWED,
        MCSEQUENCE,
        case when NUMERICGRADE is null and TRANSLATIONTABLEGRADEID is null
            then 1
            else 0
        end as GRADEISBLANK,
    SECTION
    from
        (select
            ID,
            MCDISPLAYNAME,
            TRANSLATIONTABLEID,
            VALUESALLOWEDCODE,
            GRADETYPECODE,
            NUMERICGRADE,
            TRANSLATIONTABLEGRADEID,
            STUDENTCLASSMEETINGGROUPID,
            COURSEGRADINGMARKINGCOLUMNID,
            LOWESTSCOREALLOWED,
            HIGHESTSCOREALLOWED,
            MCSEQUENCE,
            ROW_NUMBER() over (partition by COURSEGRADINGMARKINGCOLUMNID order by ID desc, STARTDATE desc) as ROW,
      SECTION
        from 
            (select
                STUDENTMARKINGCOLUMNGRADE.ID,
                MARKINGCOLUMN.DISPLAYNAME MCDISPLAYNAME,
                COURSEGRADINGMARKINGCOLUMN.TRANSLATIONTABLEID,
                (Select Case COURSEGRADINGMARKINGCOLUMN.GRADED
                    when 0 then 0
                    else (Case MarkingColumn.ALLTERMS
                        When 1 Then 1
                        else (Case When (
                                    select ID
                                    from dbo.MARKINGCOLUMNTERM
                                    where TERM.TERMNAMECODEID = MARKINGCOLUMNTERM.TERMNAMECODEID
                                        and MARKINGCOLUMNTERM.MARKINGCOLUMNID = MARKINGCOLUMN.ID
                                    ) is null
                            then 0
                            else 1
                        End)
                    End)
                End) GRADED,
                COURSEGRADINGMARKINGCOLUMN.VALUESALLOWEDCODE,
                coalesce(STUDENTMARKINGCOLUMNGRADE.GRADETYPECODE,'') as GRADETYPECODE,
                STUDENTMARKINGCOLUMNGRADE.NUMERICGRADEVALUE as NUMERICGRADE,
                STUDENTMARKINGCOLUMNGRADE.TRANSLATIONTABLEGRADEID,
                STUDENTCLASSMEETINGGROUP.ID as STUDENTCLASSMEETINGGROUPID,
                COURSEGRADINGMARKINGCOLUMN.ID as COURSEGRADINGMARKINGCOLUMNID,
                TRANSLATIONTABLE.LOWESTSCOREALLOWED,
                TRANSLATIONTABLE.HIGHESTSCOREALLOWED,
                MARKINGCOLUMN.SEQUENCE as MCSEQUENCE,
                CLASSMEETINGGROUP.STARTDATE,
        CLASS.SECTION
            from dbo.STUDENTCLASSMEETINGGROUP
                inner join dbo.CLASSMEETINGGROUP on STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID        
                inner join dbo.CLASS on CLASSMEETINGGROUP.CLASSID = CLASS.ID
                inner join dbo.COURSE on CLASS.COURSEID = COURSE.ID
                inner join dbo.ACADEMICYEAR on ACADEMICYEAR.SCHOOLID = COURSE.SCHOOLID
                inner join dbo.SESSION on ACADEMICYEAR.ID = SESSION.ACADEMICYEARID
                inner join dbo.TERM on SESSION.ID = TERM.SESSIONID
                    and TERM.STARTDATE = CLASSMEETINGGROUP.STARTDATE
                    and TERM.ENDDATE = CLASSMEETINGGROUP.ENDDATE
                inner join dbo.V_COURSEGRADING as COURSEGRADING on COURSEGRADING.COURSEID = CLASS.COURSEID
                    and COURSEGRADING.STARTDATE <= CLASS.STARTDATE
                    and COURSEGRADING.ENDDATE >= CLASS.ENDDATE
                inner join dbo.COURSEGRADINGMARKINGCOLUMN on COURSEGRADING.ID = COURSEGRADINGMARKINGCOLUMN.COURSEGRADINGID
                left join dbo.TRANSLATIONTABLE on COURSEGRADINGMARKINGCOLUMN.TRANSLATIONTABLEID = TRANSLATIONTABLE.ID
                inner join dbo.MARKINGCOLUMN on COURSEGRADINGMARKINGCOLUMN.MARKINGCOLUMNID = MARKINGCOLUMN.ID
                left join dbo.STUDENTMARKINGCOLUMNGRADE on STUDENTCLASSMEETINGGROUP.ID = STUDENTMARKINGCOLUMNGRADE.STUDENTCLASSMEETINGGROUPID
                    and STUDENTMARKINGCOLUMNGRADE.COURSEGRADINGMARKINGCOLUMNID = COURSEGRADINGMARKINGCOLUMN.ID
            where STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID = @STUDENTCOURSEID
                and STUDENTCLASSMEETINGGROUP.STATUSCODE = @STATUSCODE) g
        where g.GRADED <> 0
        ) h
    where h.ROW = 1
    order by MCSEQUENCE

    return
end