UFN_CLASSMEETINGTEMPLATE_GETTIMELIST_FROMCLASSMEETINGGROUP

create function dbo.UFN_CLASSMEETINGTEMPLATE_GETTIMELIST_FROMCLASSMEETINGGROUP
(
    @CLASSMEETINGGROUPID uniqueidentifier
)
returns nvarchar(1000)
as begin
    declare @RETVAL nvarchar(1000)

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

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