USP_DATALIST_STUDENT_SKILL_SCHOOL

CREATE procedure dbo.USP_DATALIST_STUDENT_SKILL_SCHOOL
(
    @ONLYCURRENT bit = 0
)
as
    set nocount on;

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

    select 
        STUDENTSKILL.ID,
        STUDENTSKILLSCHOOL.ID as STUDENTSKILLSCHOOLID,
        CONSTITUENT.KEYNAME,
        STUDENTSKILL.NAME,
        STUDENTSKILLRATINGGROUP.NAME as RATINGNAME,
        @CURRENTDATE,--STUDENTSKILL.STARTDATE,

        @CURRENTDATE,--STUDENTSKILL.ENDDATE,

        '' as STUDENTSKILLMARKINGCOLUMNS
       --(select stuff(( select '; ' + MARKINGCOLUMNDESCRIPTIONCODE.DESCRIPTION

       --        from dbo.STUDENTSKILLMARKINGCOLUMN

       --            join dbo.MARKINGCOLUMNDESCRIPTIONCODE on MARKINGCOLUMNDESCRIPTIONCODE.ID = STUDENTSKILLMARKINGCOLUMN.MARKINGCOLUMNDESCRIPTIONCODEID

       --        where STUDENTSKILLMARKINGCOLUMN.STUDENTSKILLID = STUDENTSKILL.ID

       --        order by MARKINGCOLUMNDESCRIPTIONCODE.SEQUENCE

       --        for xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, '')) as STUDENTSKILLMARKINGCOLUMNS

    from dbo.STUDENTSKILLSCHOOL
        join dbo.STUDENTSKILL on STUDENTSKILL.ID = STUDENTSKILLSCHOOL.STUDENTSKILLID
        join dbo.SCHOOL on SCHOOL.ID = STUDENTSKILLSCHOOL.SCHOOLID
        join dbo.CONSTITUENT on CONSTITUENT.ID = SCHOOL.ID
        join dbo.STUDENTSKILLRATINGGROUP on STUDENTSKILLRATINGGROUP.ID = STUDENTSKILL.STUDENTSKILLRATINGGROUPID
    --where @CURRENTDATE is null or (STUDENTSKILL.STARTDATE <= @CURRENTDATE and (STUDENTSKILL.ENDDATE is null or @CURRENTDATE <= STUDENTSKILL.ENDDATE))

    --order by CONSTITUENT.KEYNAME, STUDENTSKILL.STARTDATE, STUDENTSKILL.NAME