UFN_CLASSMEETINGTEMPLATE_GETTIMELIST_FROMBLOCK

create function dbo.UFN_CLASSMEETINGTEMPLATE_GETTIMELIST_FROMBLOCK
(
    @PATTERNBLOCKID uniqueidentifier
)
returns nvarchar(1000)
as begin
    declare @RETVAL nvarchar(1000)

    select @RETVAL =
        (select
            CYCLEDAY.CODE + ' ' + case when [STARTPERIOD].ID is null
                then dbo.UFN_HOURMINUTE_DISPLAYTIME(PATTERNBLOCKMEETING.STARTTIME) + ' - '
                    + dbo.UFN_HOURMINUTE_DISPLAYTIME(PATTERNBLOCKMEETING.ENDTIME)
                else [STARTPERIOD].PERIOD + 
                    case when [STARTPERIOD].ID != [ENDPERIOD].ID
                        then '-' + [ENDPERIOD].PERIOD
                        else ''
                    end
            end + '; ' as [text()]
        from dbo.PATTERNBLOCKMEETING
            inner join dbo.TIMETABLEDAY on PATTERNBLOCKMEETING.TIMETABLEDAYID = TIMETABLEDAY.ID
            inner join dbo.CYCLEDAY on TIMETABLEDAY.CYCLEDAYID = CYCLEDAY.ID
            left join dbo.TIMETABLEDAYPERIOD [STARTPERIOD] on PATTERNBLOCKMEETING.TIMETABLEDAYPERIODID = [STARTPERIOD].ID
            left join dbo.TIMETABLEDAYPERIOD [ENDPERIOD]
                on TIMETABLEDAY.ID = [ENDPERIOD].TIMETABLEDAYID
                and PATTERNBLOCKMEETING.ENDTIME = [ENDPERIOD].ENDTIME
        where PATTERNBLOCKMEETING.PATTERNBLOCKID = @PATTERNBLOCKID
        order by CYCLEDAY.SEQUENCE, PATTERNBLOCKMEETING.STARTTIME
        for xml path(''))

    return substring(@RETVAL, 1, len(@RETVAL)-1)
end