USP_SIMPLEDATALIST_EDITSTUDENTSCHEDULE_CLASS

CREATE procedure dbo.USP_SIMPLEDATALIST_EDITSTUDENTSCHEDULE_CLASS
(
    @COURSEID uniqueidentifier,
    @FILTERBY_TERMID uniqueidentifier,
    @ENROLLMENT_TERMID uniqueidentifier,
    @STARTTERMONLY bit = null,
    @CURRENTCLASSID uniqueidentifier = null
)
as
    if @COURSEID is null
        select top(0) '' as VALUE, '' as LABEL
    else
        select
            CLASS.ID as VALUE,
            CLASS.SECTION + ' (' + cast(
                coalesce((
                    select MAX(CT)
                    from (
                        select
                            COUNT(STUDENTCLASSMEETINGGROUP.ID) as CT
                        from dbo.STUDENTCLASSMEETINGGROUP
                            inner join dbo.CLASSMEETINGGROUP on dbo.STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID = dbo.CLASSMEETINGGROUP.ID
                        where CLASSMEETINGGROUP.CLASSID = CLASS.ID
                            and STUDENTCLASSMEETINGGROUP.STATUSCODE = 0    
                            and ENROLLMENT_CLASSMEETINGGROUP.STARTDATE <= CLASSMEETINGGROUP.STARTDATE
                        group by CLASSMEETINGGROUP.ID
                    ) T
                ), 0)
            as nvarchar(20)) + ')' +
                coalesce(' ' + 
                    (select stuff(
                        (select '; ' + CONSTITUENT.KEYNAME +
                            case when CONSTITUENT.FIRSTNAME is null or CONSTITUENT.FIRSTNAME = ''
                                then ''
                                else ', ' + CONSTITUENT.FIRSTNAME
                            end as [text()]
                        from dbo.FACULTYCLASSMEETINGGROUP
                            inner join dbo.FACULTYCOURSE on FACULTYCLASSMEETINGGROUP.FACULTYCOURSEID = FACULTYCOURSE.ID
                            inner join dbo.CONSTITUENT on FACULTYCOURSE.FACULTYID = CONSTITUENT.ID
                        where FACULTYCLASSMEETINGGROUP.CLASSMEETINGGROUPID = ENROLLMENT_CLASSMEETINGGROUP.ID
                        group by CONSTITUENT.ID, CONSTITUENT.KEYNAME, CONSTITUENT.FIRSTNAME
                        order by CONSTITUENT.KEYNAME, CONSTITUENT.FIRSTNAME asc
                        for xml path('')),
                    1, 2, ''))
                , '') as LABEL
        from dbo.CLASS

            inner join dbo.CLASSMEETINGGROUP FILTERBY_CLASSMEETINGGROUP on
                CLASS.ID = FILTERBY_CLASSMEETINGGROUP.CLASSID and
                (@STARTTERMONLY is null or @STARTTERMONLY <> 1 or CLASS.STARTDATE = FILTERBY_CLASSMEETINGGROUP.STARTDATE)
            inner join dbo.TERM FILTERBY_TERM on
                @FILTERBY_TERMID = FILTERBY_TERM.ID and
                FILTERBY_CLASSMEETINGGROUP.STARTDATE = FILTERBY_TERM.STARTDATE and
                FILTERBY_CLASSMEETINGGROUP.ENDDATE = FILTERBY_TERM.ENDDATE

            inner join dbo.CLASSMEETINGGROUP ENROLLMENT_CLASSMEETINGGROUP on
                CLASS.ID = ENROLLMENT_CLASSMEETINGGROUP.CLASSID
            inner join dbo.TERM ENROLLMENT_TERM on
                @ENROLLMENT_TERMID = ENROLLMENT_TERM.ID and
                ENROLLMENT_CLASSMEETINGGROUP.STARTDATE = ENROLLMENT_TERM.STARTDATE and
                ENROLLMENT_CLASSMEETINGGROUP.ENDDATE = ENROLLMENT_TERM.ENDDATE

        where CLASS.COURSEID = @COURSEID
            and (@CURRENTCLASSID is null or CLASS.ID <> @CURRENTCLASSID)
        order by CLASS.SECTION