USP_DATALIST_COURSE_STUDENT_SKILL

CREATE procedure dbo.USP_DATALIST_COURSE_STUDENT_SKILL
(
    @COURSEID uniqueidentifier,
    @ONLYCURRENT bit = 0,
    @ACADEMICYEARNAMECODEID uniqueidentifier = null
)
as
    set nocount on;

    declare @CURRENTDATE datetime
    if @ONLYCURRENT = 1
        set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

    declare @MINSEQUENCE int;
    declare @MAXSEQUENCE int;

    select 
        @MINSEQUENCE = min(SEQUENCE),
        @MAXSEQUENCE = max(SEQUENCE)
    from 
        dbo.STUDENTSKILLCOURSE
    where STUDENTSKILLCOURSE.COURSEID = @COURSEID 
        and (@CURRENTDATE is null 
            or (STUDENTSKILLCOURSE.STARTDATE <= @CURRENTDATE and (STUDENTSKILLCOURSE.ENDDATE is null or @CURRENTDATE <= STUDENTSKILLCOURSE.ENDDATE)))

    select 
        STUDENTSKILLCOURSE.ID,
        STUDENTSKILL.ID as STUDENTSKILLID,
        STUDENTSKILL.NAME as 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 STUDENTSKILLMARKINGCOLUMNS,
        STUDENTSKILLCOURSE.SEQUENCE,
        case when STUDENTSKILLCOURSE.SEQUENCE = @MINSEQUENCE then 1 else 0 end as ISMINSEQUENCE,
        case when STUDENTSKILLCOURSE.SEQUENCE = @MAXSEQUENCE then 1 else 0 end as ISMAXSEQUENCE
    from dbo.STUDENTSKILLCOURSE
        join dbo.STUDENTSKILL on STUDENTSKILLCOURSE.STUDENTSKILLID = STUDENTSKILL.ID
        left join dbo.STUDENTSKILLCATEGORYCODE on STUDENTSKILL.STUDENTSKILLCATEGORYCODEID = STUDENTSKILLCATEGORYCODE.ID
        join dbo.STUDENTSKILLRATINGGROUP on STUDENTSKILL.STUDENTSKILLRATINGGROUPID = STUDENTSKILLRATINGGROUP.ID
        join dbo.COURSE on STUDENTSKILLCOURSE.COURSEID = COURSE.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 STUDENTSKILLCOURSE.COURSEID = @COURSEID 
        and (@CURRENTDATE is null 
            or (STUDENTSKILLCOURSE.STARTDATE <= @CURRENTDATE and (STUDENTSKILLCOURSE.ENDDATE is null or @CURRENTDATE <= STUDENTSKILLCOURSE.ENDDATE)))            
        and ((@ACADEMICYEARNAMECODEID is null) or
            (ACADEMICYEAR.ACADEMICYEARNAMECODEID = @ACADEMICYEARNAMECODEID))        
    order by STUDENTSKILLCOURSE.SEQUENCE ASC