USP_DATALIST_STUDENT_SKILL_COURSE

CREATE procedure dbo.USP_DATALIST_STUDENT_SKILL_COURSE
(
    @ACADEMICYEARNAMECODEID uniqueidentifier = null,
    @STUDENTSKILLCATEGORYCODEID uniqueidentifier = null,
    @GRADELEVELID uniqueidentifier = null
)
as
    set nocount on;

    select 
        STUDENTSKILL.ID,
        STUDENTSKILLCOURSE.ID as STUDENTSKILLCOURSEID,
        COURSE.NAME as COURSENAME,
        COURSE.ID as COURSEID,
        STUDENTSKILL.NAME 'SKILLNAME',
        STUDENTSKILLRATINGGROUP.NAME as RATINGNAME,
        STUDENTSKILLCATEGORYCODE.DESCRIPTION as CATEGORY,
        (select ACADEMICYEARNAMECODE.DESCRIPTION from dbo.ACADEMICYEARNAMECODE 
            inner join dbo.ACADEMICYEAR on ACADEMICYEARNAMECODE.ID = ACADEMICYEAR.ACADEMICYEARNAMECODEID 
            where 
                (STUDENTSKILLCOURSE.STARTDATE = ACADEMICYEAR.STARTDATE) and
                (ACADEMICYEAR.SCHOOLID = COURSE.SCHOOLID)) 'STARTYEAR',
        (select ACADEMICYEARNAMECODE.DESCRIPTION from dbo.ACADEMICYEARNAMECODE 
            inner join dbo.ACADEMICYEAR on ACADEMICYEARNAMECODE.ID = ACADEMICYEAR.ACADEMICYEARNAMECODEID 
            where 
                (STUDENTSKILLCOURSE.ENDDATE = ACADEMICYEAR.ENDDATE) and
                (ACADEMICYEAR.SCHOOLID = COURSE.SCHOOLID)) 'ENDYEAR',
       (select stuff(( select '; ' + MARKINGCOLUMNDESCRIPTIONCODE.DESCRIPTION
               from dbo.STUDENTSKILLCOURSEMARKINGCOLUMN
                   join dbo.MARKINGCOLUMNDESCRIPTIONCODE on MARKINGCOLUMNDESCRIPTIONCODE.ID = STUDENTSKILLCOURSEMARKINGCOLUMN.MARKINGCOLUMNDESCRIPTIONCODEID
               where STUDENTSKILLCOURSEMARKINGCOLUMN.STUDENTSKILLCOURSEID = STUDENTSKILLCOURSE.ID
               order by MARKINGCOLUMNDESCRIPTIONCODE.SEQUENCE
               for xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, '')) as STUDENTSKILLCOURSEMARKINGCOLUMNS,
        STUDENTSKILLCOURSE.SEQUENCE                       
    from dbo.STUDENTSKILLCOURSE
        join dbo.STUDENTSKILL on STUDENTSKILL.ID = STUDENTSKILLCOURSE.STUDENTSKILLID
        join dbo.COURSE on COURSE.ID = STUDENTSKILLCOURSE.COURSEID
        join dbo.STUDENTSKILLRATINGGROUP on STUDENTSKILLRATINGGROUP.ID = STUDENTSKILL.STUDENTSKILLRATINGGROUPID
        left join dbo.STUDENTSKILLCATEGORYCODE on STUDENTSKILL.STUDENTSKILLCATEGORYCODEID = STUDENTSKILLCATEGORYCODE.ID        
        left join dbo.ACADEMICYEAR on (@ACADEMICYEARNAMECODEID is not null) and
            (ACADEMICYEAR.STARTDATE >= STUDENTSKILLCOURSE.STARTDATE) and 
            ((ACADEMICYEAR.ENDDATE <= STUDENTSKILLCOURSE.ENDDATE) or (STUDENTSKILLCOURSE.ENDDATE is null)) and
            (ACADEMICYEAR.SCHOOLID = COURSE.SCHOOLID)
    where 
        ((@STUDENTSKILLCATEGORYCODEID is null) or (@STUDENTSKILLCATEGORYCODEID = STUDENTSKILLCATEGORYCODE.ID)) and
        ((@GRADELEVELID is null) or 
            (@GRADELEVELID in 
                (select SCHOOLGRADELEVEL.GRADELEVELID from dbo.SCHOOLGRADELEVEL
                inner join dbo.COURSEGRADELEVEL on SCHOOLGRADELEVEL.ID = COURSEGRADELEVEL.SCHOOLGRADELEVELID
                where COURSEGRADELEVEL.COURSEID = COURSE.ID))) and
        ((@ACADEMICYEARNAMECODEID is null) or
            (ACADEMICYEAR.ACADEMICYEARNAMECODEID = @ACADEMICYEARNAMECODEID))            
    order by COURSE.NAME, STUDENTSKILLCOURSE.SEQUENCE