V_QUERY_STUDENT_MARKINGCOLUMN_GRADES

/*
Generated by Blackbaud AppFx Platform
Date:  8/2/2010 7:12:30 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=2.7.1654.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_STUDENT_MARKINGCOLUMN_GRADES AS



       select
        STUDENTMARKINGCOLUMNGRADE.ID,
        dbo.CLASS.ID AS 'ClassID',
        (case dbo.STUDENTMARKINGCOLUMNGRADE.GRADETYPECODE
            when 2 then cast(dbo.STUDENTMARKINGCOLUMNGRADE.NUMERICGRADEVALUE AS nvarchar(20))
            when 1 then cast(dbo.TRANSLATIONTABLEGRADE.GRADE as nvarchar(20))
        end) AS GRADE,
        (case dbo.STUDENTMARKINGCOLUMNGRADE.GRADETYPECODE
            when 2 then STUDENTMARKINGCOLUMNGRADE.NUMERICGRADEVALUE
            when 1 then dbo.TRANSLATIONTABLEGRADE.NUMERICEQUIVALENT
        end) AS NUMERICEQUIVALENT,
        (dbo.MARKINGCOLUMNSET.NAME + ' - ' + dbo.MARKINGCOLUMN.DISPLAYNAME) AS MARKINGCOLUMN,
        ACADEMICYEARNAMECODE.DESCRIPTION AS ACADEMICYEARNAME,
        SESSIONNAMECODE.DESCRIPTION AS SESSIONNAME,
        CONS_FAC.ID FACULTYID,
        CONS_SCH.NAME SCHOOL_NAME,
        ADDEDBY.APPLICATIONNAME as [ADDEDBY_APPLICATION],
        ADDEDBY.USERNAME as [ADDEDBY_USERNAME],
        CHANGEDBY.APPLICATIONNAME as [CHANGEDBY_APPLICATION],
        CHANGEDBY.USERNAME as [CHANGEDBY_USERNAME],
        STUDENTMARKINGCOLUMNGRADE.DATEADDED,
        STUDENTMARKINGCOLUMNGRADE.DATECHANGED,
        STUDENTMARKINGCOLUMNGRADE.TSLONG,
        STUDENT.ID STUDENTID,
        COURSE.ID COURSEID
       from
        DBO.STUDENTMARKINGCOLUMNGRADE 
        INNER JOIN DBO.STUDENTCLASSMEETINGGROUP 
            ON dbo.STUDENTCLASSMEETINGGROUP.ID = dbo.STUDENTMARKINGCOLUMNGRADE.STUDENTCLASSMEETINGGROUPID
        INNER JOIN dbo.STUDENTCOURSE 
            ON dbo.STUDENTCOURSE.ID = dbo.STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID
        INNER JOIN DBO.STUDENT 
            ON DBO.STUDENT.ID = DBO.STUDENTCOURSE.STUDENTID            
        INNER JOIN dbo.CLASSMEETINGGROUP 
            ON dbo.CLASSMEETINGGROUP.ID = dbo.STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID    
        INNER JOIN dbo.CLASS 
            ON DBO.CLASS.ID = dbo.CLASSMEETINGGROUP.CLASSID    
        INNER JOIN dbo.COURSE 
            ON dbo.COURSE.ID = dbo.CLASS.COURSEID
        LEFT JOIN dbo.TRANSLATIONTABLEGRADE 
            ON dbo.TRANSLATIONTABLEGRADE.ID = dbo.STUDENTMARKINGCOLUMNGRADE.TRANSLATIONTABLEGRADEID    
        LEFT JOIN dbo.TRANSLATIONTABLE
            ON dbo.TRANSLATIONTABLE.ID = dbo.TRANSLATIONTABLEGRADE.TRANSLATIONTABLEID            
        INNER JOIN dbo.COURSEGRADINGMARKINGCOLUMN 
            ON dbo.COURSEGRADINGMARKINGCOLUMN.ID = dbo.STUDENTMARKINGCOLUMNGRADE.COURSEGRADINGMARKINGCOLUMNID
        INNER JOIN dbo.COURSEGRADING
            ON COURSEGRADINGMARKINGCOLUMN.COURSEGRADINGID = COURSEGRADING.ID
        INNER JOIN dbo.MARKINGCOLUMN
            ON dbo.MARKINGCOLUMN.ID = dbo.COURSEGRADINGMARKINGCOLUMN.MARKINGCOLUMNID
        LEFT JOIN DBO.MARKINGCOLUMNSET
            ON DBO.MARKINGCOLUMNSET.ID = DBO.MARKINGCOLUMN.MARKINGCOLUMNSETID
        LEFT JOIN dbo.FACULTYCLASSMEETINGGROUP 
            ON dbo.FACULTYCLASSMEETINGGROUP.CLASSMEETINGGROUPID = dbo.CLASSMEETINGGROUP.ID
        LEFT JOIN dbo.FACULTYCOURSE 
            ON dbo.FACULTYCOURSE.ID = dbo.FACULTYCLASSMEETINGGROUP.FACULTYCOURSEID 
            AND dbo.FACULTYCOURSE.COURSEID = dbo.COURSE.ID
        LEFT JOIN dbo.FACULTY 
            ON dbo.FACULTY.ID = dbo.FACULTYCOURSE.FACULTYID    
        LEFT JOIN dbo.CONSTITUENT CONS_FAC 
            ON CONS_FAC.ID = dbo.FACULTY.ID
        INNER JOIN dbo.CONSTITUENT CONS_SCH 
            ON CONS_SCH.ID = dbo.COURSE.SCHOOLID
        INNER JOIN DBO.COURSERESTRICTION 
            ON DBO.COURSERESTRICTION.COURSEID = DBO.COURSE.ID
        INNER JOIN SESSION 
             ON SESSION.ID = DBO.UFN_SESSION_GET_FORSCHOOL_BYDATE(COURSE.SCHOOLID, COURSERESTRICTION.STARTDATE, COURSERESTRICTION.ENDDATE)
            and SESSION.ID = DBO.UFN_SESSION_GET_FORSCHOOL_BYDATE(COURSE.SCHOOLID, COURSEGRADING.STARTDATE, COURSEGRADING.ENDDATE)
        INNER JOIN SESSIONNAMECODE 
            ON SESSIONNAMECODE.ID = SESSION.SESSIONNAMECODEID 
        INNER JOIN ACADEMICYEAR 
            ON ACADEMICYEAR.ID = SESSION.ACADEMICYEARID 
        INNER JOIN ACADEMICYEARNAMECODE 
            ON ACADEMICYEARNAMECODE.ID = ACADEMICYEAR.ACADEMICYEARNAMECODEID 
        LEFT JOIN DBO.CHANGEAGENT AS [ADDEDBY] 
            ON [ADDEDBY].ID = STUDENTMARKINGCOLUMNGRADE.ADDEDBYID
        LEFT JOIN DBO.CHANGEAGENT AS [CHANGEDBY] 
            ON [CHANGEDBY].ID = STUDENTMARKINGCOLUMNGRADE.CHANGEDBYID