USP_DATALIST_CLASS_MEETINGSCALENDAR

CREATE procedure dbo.USP_DATALIST_CLASS_MEETINGSCALENDAR
(
    @CLASSID uniqueidentifier
)
as
    set nocount on

    declare @CURRENTDATE date = getdate()

    select
        T.ID,
        T.CLASSID,
        T.CYCLEDAYID,
        @CURRENTDATE as STARTDATE,
        case when T.TIMETABLEDAYPERIODID is not null
            then dbo.UFN_CLASSMEETINGTEMPLATE_GETPERIODSTRING(T.ID, T.LENGTHINPERIODS) + coalesce(' (' + PATTERNBLOCK.NAME + ')', '')            
            else coalesce(PATTERNBLOCK.NAME, '')
        end as DESCRIPTION,
        T.STARTTIME,
        T.ENDTIME
    from (
        select
            CLASSMEETINGTEMPLATE.ID,
            CLASSMEETINGTEMPLATE.CYCLEDAYID,
            CLASSMEETINGTEMPLATE.STARTTIME,
            CLASSMEETINGTEMPLATE.ENDTIME,
            CLASSMEETINGTEMPLATE.LENGTHINPERIODS,
            CLASSMEETINGTEMPLATE.TIMETABLEDAYPERIODID,  
            ROW_NUMBER() OVER (PARTITION BY
                CLASSMEETINGTEMPLATE.CYCLEDAYID,
                CLASSMEETINGTEMPLATE.STARTTIME,
                CLASSMEETINGTEMPLATE.ENDTIME,
                CLASSMEETINGTEMPLATE.LENGTHINPERIODS,
                CLASSMEETINGTEMPLATE.TIMETABLEDAYPERIODID  
                ORDER BY CLASSMEETINGTEMPLATE.DATEADDED DESC
            ) ROW,
            CLASSMEETINGGROUP.PATTERNBLOCKID,
            CLASSMEETINGGROUP.CLASSID
        from dbo.CLASSMEETINGTEMPLATE
            inner join dbo.CLASSMEETINGGROUP on CLASSMEETINGTEMPLATE.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
        where CLASSMEETINGGROUP.CLASSID = @CLASSID
    ) T
        inner join dbo.CYCLEDAY on T.CYCLEDAYID = CYCLEDAY.ID
        left join dbo.TIMETABLEDAYPERIOD on TIMETABLEDAYPERIOD.ID = T.TIMETABLEDAYPERIODID
        left join dbo.PATTERNBLOCK on PATTERNBLOCK.ID = T.PATTERNBLOCKID 
    where T.ROW = 1
    order by CYCLEDAY.SEQUENCE, T.STARTTIME