UFN_TRANSLATIONTABLE_GETGRADE

CREATE function dbo.UFN_TRANSLATIONTABLE_GETGRADE( @TRANSLATIONTABLEID uniqueidentifier )
returns table
as return(

select
    TTG.ID,
    TTG.GRADE,
    TTG.NUMERICEQUIVALENT,
    TTG.CREDITAWARDED,
    TTG.HIGHESTSCORE,
    TTG.LOWESTSCORE,
    case when count(STUDENTMARKINGCOLUMNGRADE.ID) > 0 then 1 else 0 end 'HASGRADES'
from dbo.TRANSLATIONTABLEGRADE TTG
left join dbo.COURSEGRADINGMARKINGCOLUMN on TTG.TRANSLATIONTABLEID = COURSEGRADINGMARKINGCOLUMN.TRANSLATIONTABLEID
left join dbo.STUDENTMARKINGCOLUMNGRADE on (COURSEGRADINGMARKINGCOLUMN.ID = STUDENTMARKINGCOLUMNGRADE.COURSEGRADINGMARKINGCOLUMNID) and 
        (STUDENTMARKINGCOLUMNGRADE.GRADEISBLANK = 0) and
        ((TTG.ID = STUDENTMARKINGCOLUMNGRADE.TRANSLATIONTABLEGRADEID) and 
        (STUDENTMARKINGCOLUMNGRADE.GRADETYPECODE = 1)) or
        ((STUDENTMARKINGCOLUMNGRADE.NUMERICGRADE between TTG.LOWESTSCORE and TTG.HIGHESTSCORE) and 
        (STUDENTMARKINGCOLUMNGRADE.GRADETYPECODE = 2))
where TTG.TRANSLATIONTABLEID = @TRANSLATIONTABLEID
group by TTG.ID, TTG.GRADE, TTG.NUMERICEQUIVALENT, TTG.CREDITAWARDED, TTG.HIGHESTSCORE, TTG.LOWESTSCORE

)