USP_REPORT_FACULTYSCHEDULE

CREATE procedure dbo.USP_REPORT_FACULTYSCHEDULE(
    @RECORDQUERYID uniqueidentifier = null,
    @RECORDID_SINGLE uniqueidentifier = null,
    @SCHOOLID uniqueidentifier = null,
    @ACADEMICYEARID uniqueidentifier = null,
    @SESSIONID uniqueidentifier = null,  
    @TERMS nvarchar(max) = null, 
    @DISPLAYBY tinyint = null,
    @INCLUDENOMEETINGS bit = null,
    @PERIODS nvarchar(max) = null,
    @CYCLEDAYS nvarchar(max) = null,
    @STARTTIME smalldatetime = null,
    @ENDTIME smalldatetime = null,
    @INTERVAL tinyint = null,
    @BREAKDOWNBY tinyint = null
)
as
begin
    declare @SESSION_STARTDATE date
    declare @SESSION_ENDDATE date

    select 
        @SESSION_STARTDATE = STARTDATE, 
        @SESSION_ENDDATE = ENDDATE 
    from 
        dbo.UFN_SESSION_GETDATES(@SESSIONID)

    declare @ACADEMICYEAR nvarchar(100)
    set @ACADEMICYEAR = dbo.UFN_ACADEMICYEAR_GETNAME(@ACADEMICYEARID) 

    declare @CYCLEDAYCOUNT int
    select @CYCLEDAYCOUNT = COUNT(1) from dbo.UFN_PARSE_STRING(@CYCLEDAYS,',')

    declare @FACULTYQUERY_IDS table (ID uniqueidentifier)
    if not @RECORDQUERYID is null
        begin
            if not exists(select ID from dbo.IDSETREGISTER where ID = @RECORDQUERYID) 
                raiserror('Selection ID set does not exist in the database.', 15, 1)

            insert into @FACULTYQUERY_IDS
                select 
                    SELECTION.ID
                from 
                    dbo.UFN_IDSETREADER_GETRESULTS_GUID(@RECORDQUERYID) SELECTION 
        end

    -- single faculty, selection or all faculty; filter by session dates and enrollment                

    declare @FACULTYIDS table (ID uniqueidentifier)
    insert into @FACULTYIDS 
        select 
            FACULTY.ID
        from dbo.FACULTY
            left join @FACULTYQUERY_IDS SELECTION 
                on SELECTION.ID = FACULTY.ID
        where (@RECORDQUERYID is null or SELECTION.ID is not null)
            and (@RECORDID_SINGLE is null or FACULTY.ID = @RECORDID_SINGLE)
            and exists 
                (select 
                    FACULTYCOURSE.FACULTYID 
                from dbo.FACULTYCOURSE 
                    inner join dbo.FACULTYCLASSMEETINGGROUP 
                        on FACULTYCLASSMEETINGGROUP.FACULTYCOURSEID = FACULTYCOURSE.ID
                    inner join dbo.CLASSMEETINGGROUP 
                        on CLASSMEETINGGROUP.ID = FACULTYCLASSMEETINGGROUP.CLASSMEETINGGROUPID
                    inner join dbo.COURSE 
                        on COURSE.ID = FACULTYCOURSE.COURSEID
                where 
                    FACULTYCOURSE.FACULTYID = FACULTY.ID and 
                    COURSE.SCHOOLID = @SCHOOLID and
                    COURSE.PRINTONSCHEDULE = 1 and
                    not (CLASSMEETINGGROUP.ENDDATE < @SESSION_STARTDATE or 
                    CLASSMEETINGGROUP.STARTDATE > @SESSION_ENDDATE)
                )

    --create map of possible terms/days/times/periods for each faculty in query...this should reflect what we see in the grid

    --only create terms for faculty that actually have meeting data...avoid empty grids

    declare @SCHEDULE table (ID int NOT NULL IDENTITY(1,1), FACULTYID uniqueidentifier, TERMID uniqueidentifier, CYCLEDAYID uniqueidentifier, PERIOD nvarchar(60), PERIOD_SEQUENCE int, LENGTHIN_TIMEROWS int, TIMEROW_COUNT int, CYCLEDAYCOUNT int, CLASSID uniqueidentifier, NAME nvarchar(32), CLASSNAME nvarchar(60), CLASS_STARTTIME smalldatetime, CLASS_ENDTIME smalldatetime, ACADEMICYEAR nvarchar(100), LENGTHINPERIODS int, PERIODCOUNT int, INTERVAL int, STARTTIME smalldatetime, ENDTIME smalldatetime, HIDEGRID bit, HIDENOMEETINGSGRID bit)

    if @BREAKDOWNBY = 0 --Periods

        begin
            insert into @SCHEDULE (FACULTYID, TERMID, CYCLEDAYID, PERIOD, PERIOD_SEQUENCE, CYCLEDAYCOUNT, ACADEMICYEAR)
            select
                FACULTY.ID,
                TERMS.ELEMENT_VALUE as TERMID,
                CYCLEDAYS.ELEMENT_VALUE as CYCLEDAYID,
                PERIODS.ELEMENT_VALUE,
                --period start time on the first cycle day with a matching period description...used to order report

                (select top 1 TIMETABLEDAYPERIOD.STARTTIME 
                     from 
                        dbo.TIMETABLEDAYPERIOD 
                         inner join dbo.TIMETABLEDAY  
                            on TIMETABLEDAY.ID = TIMETABLEDAYPERIOD.TIMETABLEDAYID 
                         inner join dbo.CYCLEDAY  
                            on CYCLEDAY.ID = TIMETABLEDAY.CYCLEDAYID 
                         inner join SESSION 
                            on SESSION.TIMETABLEID = TIMETABLEDAY.TIMETABLEID 
                     where 
                         SESSION.ID = @SESSIONID and TIMETABLEDAYPERIOD.PERIOD = PERIODS.ELEMENT_VALUE
                     order by CYCLEDAY.SEQUENCE) as PERIOD_SEQUENCE,
                CYCLEDAYCOUNT = @CYCLEDAYCOUNT,
                ACADEMICYEAR = @ACADEMICYEAR
            from @FACULTYIDS as FACULTY
                cross join dbo.UFN_PARSE_STRING(@CYCLEDAYS,',') CYCLEDAYS
                cross join dbo.UFN_PARSE_STRING(@PERIODS,',') as PERIODS
                cross join dbo.UFN_PARSE_STRING(@TERMS,',') TERMS
                where exists(
                    select FACULTYCOURSE.ID 
                    from dbo.FACULTYCOURSE 
                        inner join dbo.FACULTYCLASSMEETINGGROUP 
                            on FACULTYCLASSMEETINGGROUP.FACULTYCOURSEID = FACULTYCOURSE.ID
                        inner join dbo.CLASSMEETINGGROUP 
                            on CLASSMEETINGGROUP.ID = FACULTYCLASSMEETINGGROUP.CLASSMEETINGGROUPID
                        inner join dbo.TERM  
                            on TERM.SESSIONID = @SESSIONID
                                and TERM.STARTDATE = CLASSMEETINGGROUP.STARTDATE
                                and TERM.ENDDATE = CLASSMEETINGGROUP.ENDDATE
                                and TERM.ID = TERMS.ELEMENT_VALUE 
                        inner join dbo.CLASSMEETINGTEMPLATE
                            on CLASSMEETINGTEMPLATE.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
                        inner join dbo.COURSE 
                            on COURSE.ID = FACULTYCOURSE.COURSEID
                    where 
                        FACULTY.ID = FACULTYCOURSE.FACULTYID and
                        COURSE.SCHOOLID = @SCHOOLID and
                        COURSE.PRINTONSCHEDULE = 1)

            --populate all period data for each class meeting

            update @SCHEDULE
                set 
                    CLASSID = CLASSMEETINGGROUP.CLASSID,
                    NAME = COURSE.[COURSEID] + ' - ' + CLASS.[SECTION],
                    CLASSNAME = CLASS.[NAME],
                    CLASS_STARTTIME = dbo.UFN_DATE_ADDHOURMINUTE('19000101', CLASSMEETINGTEMPLATE.STARTTIME),
                    CLASS_ENDTIME = dbo.UFN_DATE_ADDHOURMINUTE('19000101', CLASSMEETINGTEMPLATE.ENDTIME),
                    LENGTHINPERIODS = CLASSMEETINGTEMPLATE.LENGTHINPERIODS,
                    PERIODCOUNT = MEETING_PERIODS.ROW
            from @SCHEDULE as SCHEDULE
                cross join dbo.CLASSMEETINGGROUP
                inner join dbo.FACULTYCLASSMEETINGGROUP           
                    on CLASSMEETINGGROUP.ID = FACULTYCLASSMEETINGGROUP.CLASSMEETINGGROUPID
                inner join dbo.FACULTYCOURSE                      
                    on FACULTYCLASSMEETINGGROUP.FACULTYCOURSEID = FACULTYCOURSE.ID
                inner join dbo.CLASS                              
                    on CLASSMEETINGGROUP.CLASSID = CLASS.ID
                inner join dbo.CLASSMEETINGTEMPLATE               
                    on CLASSMEETINGTEMPLATE.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
                inner join dbo.COURSE                             
                    on CLASS.COURSEID = COURSE.ID
                inner join dbo.TERM                               
                    on TERM.SESSIONID = @SESSIONID
                    and TERM.STARTDATE = CLASSMEETINGGROUP.STARTDATE
                    and TERM.ENDDATE = CLASSMEETINGGROUP.ENDDATE
                inner join dbo.CYCLEDAY                           
                    on CYCLEDAY.ID = CLASSMEETINGTEMPLATE.CYCLEDAYID                                                
                cross apply --get all periods for the class; sql copied from UFN_CLASSMEETINGTEMPLATE_GETPERIODSTRING

                    (select
                        T.PERIOD, T.ROW
                    from
                        (select
                            [PEER].PERIOD as PERIOD,
                            [PEER].STARTTIME,
                            ROW_NUMBER() over (order by [PEER].STARTTIME) as ROW                
                        from dbo.CLASSMEETINGTEMPLATE CMT
                            left join dbo.TIMETABLEDAYPERIOD [PRIMARY] 
                                on CMT.TIMETABLEDAYPERIODID = [PRIMARY].ID 
                            left join dbo.TIMETABLEDAYPERIOD [PEER] 
                                on [PRIMARY].TIMETABLEDAYID = [PEER].TIMETABLEDAYID
                        where CMT.ID = CLASSMEETINGTEMPLATE.ID
                            and ([PRIMARY].ID is null or [PEER].STARTTIME >= [PRIMARY].STARTTIME)
                        ) T
                    where ROW <= CLASSMEETINGTEMPLATE.LENGTHINPERIODS) MEETING_PERIODS    
            where 
                TERM.ID = SCHEDULE.TERMID and 
                CYCLEDAY.ID = SCHEDULE.CYCLEDAYID and
                FACULTYCOURSE.FACULTYID = SCHEDULE.FACULTYID and
                SCHEDULE.PERIOD = MEETING_PERIODS.PERIOD and 
                COURSE.PRINTONSCHEDULE = 1    
        end
    else --Interval

        begin
            --number of time slices/rows in each grid

            declare @FACTOR float(2) = (1440/@INTERVAL)
            declare @ADJUSTED_STARTTIME smalldatetime = Floor(Cast(@STARTTIME as float(2))*@FACTOR)/@FACTOR
            declare @ADJUSTED_ENDTIME smalldatetime = Ceiling(Cast(@ENDTIME as float(2))*@FACTOR)/@FACTOR

            declare @TIME_ROWS int = DateDiff(minute, @ADJUSTED_STARTTIME, @ADJUSTED_ENDTIME) / @INTERVAL

            declare @TIME_INTERVALS table (INTERVAL int, STARTTIME smalldatetime, ENDTIME smalldatetime)
            insert into @TIME_INTERVALS
                select
                    NUM,
                    DateAdd(minute, NUM * @INTERVAL, @ADJUSTED_STARTTIME), 
                    DateAdd(minute, (NUM + 1) * @INTERVAL, @ADJUSTED_STARTTIME)
                from 
                    dbo.NUMBERS with (nolock)
                where 
                    NUM < @TIME_ROWS

            insert into @SCHEDULE (FACULTYID, TERMID, CYCLEDAYID, INTERVAL, STARTTIME, ENDTIME, CYCLEDAYCOUNT, ACADEMICYEAR)
            select
                FACULTY.ID,
                TERMS.ELEMENT_VALUE as TERMID,
                CYCLEDAYS.ELEMENT_VALUE as CYCLEDAYID,
                TIMES.INTERVAL,
                TIMES.STARTTIME,
                TIMES.ENDTIME,
                CYCLEDAYCOUNT = @CYCLEDAYCOUNT,
                ACADEMICYEAR = @ACADEMICYEAR
            from @FACULTYIDS as FACULTY
                cross join dbo.UFN_PARSE_STRING(@TERMS,',') TERMS
                cross join dbo.UFN_PARSE_STRING(@CYCLEDAYS,',') CYCLEDAYS
                cross join @TIME_INTERVALS as TIMES
                where exists(
                    select FACULTYCOURSE.ID 
                    from dbo.FACULTYCOURSE 
                        inner join dbo.FACULTYCLASSMEETINGGROUP 
                            on FACULTYCLASSMEETINGGROUP.FACULTYCOURSEID = FACULTYCOURSE.ID
                        inner join dbo.CLASSMEETINGGROUP 
                            on CLASSMEETINGGROUP.ID = FACULTYCLASSMEETINGGROUP.CLASSMEETINGGROUPID
                        inner join dbo.TERM  
                            on TERM.SESSIONID = @SESSIONID
                                and TERM.STARTDATE = CLASSMEETINGGROUP.STARTDATE
                                and TERM.ENDDATE = CLASSMEETINGGROUP.ENDDATE
                                and TERM.ID = TERMS.ELEMENT_VALUE 
                        inner join dbo.CLASSMEETINGTEMPLATE
                            on CLASSMEETINGTEMPLATE.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
                        inner join dbo.COURSE 
                            on COURSE.ID = FACULTYCOURSE.COURSEID
                    where 
                        FACULTY.ID = FACULTYCOURSE.FACULTYID and
                        COURSE.SCHOOLID = @SCHOOLID and
                        COURSE.PRINTONSCHEDULE = 1)

            --populate time data for each class meeting

            update @SCHEDULE
                set 
                    CLASSID = I.CLASSID,
                    NAME = I.NAME,
                    CLASSNAME = I.CLASSNAME,
                    CLASS_STARTTIME = I.CLASS_STARTTIME,
                    CLASS_ENDTIME = I.CLASS_ENDTIME,
                    LENGTHIN_TIMEROWS = I.LENGTHIN_TIMEROWS,
                    TIMEROW_COUNT = I.TIMEROW_COUNT,
                    PERIOD = I.PERIOD
            from @SCHEDULE as SCHEDULE
            join (select 
                    ID = T.ID,
                    CLASSID = T.CLASSID,
                    NAME = T.NAME,
                    CLASSNAME = T.CLASSNAME,
                    CLASS_STARTTIME,
                    CLASS_ENDTIME,
                    LENGTHIN_TIMEROWS = 
                        case when T.CLASSID is null 
                            then null
                            else COUNT(T.FACULTYID) OVER (PARTITION BY T.FACULTYID, T.TERMID, T.CYCLEDAYCODE, T.CLASSID) end,
                    TIMEROW_COUNT = 
                        case when T.CLASSID is null 
                            then null
                            else ROW_NUMBER() OVER (PARTITION BY T.FACULTYID, T.TERMID, T.CYCLEDAYCODE, T.CLASSID order by T.INTERVAL) end,
                    PERIOD = T.PERIOD
                from
                    (select 
                        SCHEDULE.ID,
                        SCHEDULE.INTERVAL,
                        SCHEDULE.STARTTIME as ROW_STARTTIME,
                        SCHEDULE.ENDTIME as ROW_ENDTIME,
                        SCHEDULE.FACULTYID as FACULTYID,
                        case when CMT.ID is null
                            then null
                            else CLASSMEETINGGROUP.CLASSID
                        end as CLASSID,
                        COURSE.[COURSEID] + ' - ' + CLASS.[SECTION] as NAME,
                        CLASS.[NAME] as CLASSNAME,
                        CMT.STARTTIME as CLASS_STARTTIME,
                        CMT.ENDTIME as CLASS_ENDTIME,
                        CYCLEDAY.CODE as CYCLEDAYCODE,
                        TERM.ID as TERMID,
                        CLASSMEETINGGROUP.ID as CLASSMEETINGGROUPID,
                        TIMETABLEDAYPERIOD.PERIOD as PERIOD,
                        ROW_NUMBER() OVER (PARTITION BY SCHEDULE.FACULTYID, TERM.ID, SCHEDULE.INTERVAL, CYCLEDAY.ID order by SCHEDULE.INTERVAL, CLASSMEETINGGROUP.ID desc) as ROWNUMBER
                    from @SCHEDULE as SCHEDULE
                        cross join dbo.CLASSMEETINGGROUP
                        inner join dbo.FACULTYCLASSMEETINGGROUP           
                            on CLASSMEETINGGROUP.ID = FACULTYCLASSMEETINGGROUP.CLASSMEETINGGROUPID
                        inner join dbo.FACULTYCOURSE                      
                            on FACULTYCLASSMEETINGGROUP.FACULTYCOURSEID = FACULTYCOURSE.ID
                            and FACULTYCOURSE.FACULTYID = SCHEDULE.FACULTYID
                        inner join dbo.CLASS                              
                            on CLASSMEETINGGROUP.CLASSID = CLASS.ID
                        inner join (
                             select 
                                 ID, 
                                 CLASSMEETINGGROUPID, 
                                 CYCLEDAYID,  
                                 TIMETABLEDAYPERIODID, 
                                 dbo.UFN_DATE_ADDHOURMINUTE('19000101', CLASSMEETINGTEMPLATE.STARTTIME) as STARTTIME, 
                                 dbo.UFN_DATE_ADDHOURMINUTE('19000101', CLASSMEETINGTEMPLATE.ENDTIME) as ENDTIME,
                                 DateDiff(minute, dbo.UFN_DATE_ADDHOURMINUTE('19000101', CLASSMEETINGTEMPLATE.STARTTIME), dbo.UFN_DATE_ADDHOURMINUTE('19000101', CLASSMEETINGTEMPLATE.ENDTIME)) as DIFF,
                                 DateAdd(minute, -@INTERVAL, dbo.UFN_DATE_ADDHOURMINUTE('19000101', CLASSMEETINGTEMPLATE.STARTTIME)) as STARTTIME_MINUS_INTERVAL
                             from dbo.CLASSMEETINGTEMPLATE) CMT     
                                on CMT.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
                        inner join dbo.COURSE                             
                            on CLASS.COURSEID = COURSE.ID 
                            and COURSE.PRINTONSCHEDULE = 1 
                        inner join dbo.TERM                               
                            on TERM.SESSIONID = @SESSIONID
                            and TERM.STARTDATE = CLASSMEETINGGROUP.STARTDATE
                            and TERM.ENDDATE = CLASSMEETINGGROUP.ENDDATE
                            and TERM.ID = SCHEDULE.TERMID
                        inner join dbo.CYCLEDAY                           
                            on CYCLEDAY.ID = CMT.CYCLEDAYID
                            and CYCLEDAY.ID = SCHEDULE.CYCLEDAYID
                        left join dbo.TIMETABLEDAYPERIOD            
                            on TIMETABLEDAYPERIOD.ID = CMT.TIMETABLEDAYPERIODID 
                    where
                        SCHEDULE.STARTTIME > CMT.STARTTIME_MINUS_INTERVAL and
                        ((SCHEDULE.ENDTIME <= CMT.ENDTIME) or 
                         ((@INTERVAL > CMT.DIFF) and (SCHEDULE.STARTTIME < CMT.ENDTIME)))
                    ) T 
                where T.ROWNUMBER = 1 
            ) I on I.ID = SCHEDULE.ID
        end

    --insert classes without meetings

    if @INCLUDENOMEETINGS = 1 
        insert into @SCHEDULE (FACULTYID, TERMID, CLASSID, NAME, CLASSNAME, ACADEMICYEAR)
            select
                FACULTY.ID,
                TERM.ID,
                CLASSID = CLASSMEETINGGROUP.CLASSID,
                NAME = COURSE.[COURSEID] + ' - ' + CLASS.[SECTION],
                CLASSNAME = CLASS.[NAME],
                ACADEMICYEAR = @ACADEMICYEAR
            from @FACULTYIDS as FACULTY
                cross join dbo.CLASSMEETINGGROUP
                inner join dbo.FACULTYCLASSMEETINGGROUP           
                    on CLASSMEETINGGROUP.ID = FACULTYCLASSMEETINGGROUP.CLASSMEETINGGROUPID
                inner join dbo.FACULTYCOURSE                      
                    on FACULTYCLASSMEETINGGROUP.FACULTYCOURSEID = FACULTYCOURSE.ID
                inner join dbo.CLASS                              
                    on CLASSMEETINGGROUP.CLASSID = CLASS.ID
                inner join dbo.COURSE                             
                    on CLASS.COURSEID = COURSE.ID
                inner join dbo.ACADEMICYEAR                       
                    on COURSE.SCHOOLID = ACADEMICYEAR.SCHOOLID
                inner join dbo.SESSION                            
                    on ACADEMICYEAR.ID = SESSION.ACADEMICYEARID
                inner join dbo.TERM                               
                    on SESSION.ID = TERM.SESSIONID
                    and TERM.STARTDATE = CLASSMEETINGGROUP.STARTDATE
                    and TERM.ENDDATE = CLASSMEETINGGROUP.ENDDATE
                inner join dbo.UFN_PARSE_STRING(@TERMS,',') TERMS 
                    on TERM.ID = TERMS.ELEMENT_VALUE
            where 
                FACULTYCOURSE.FACULTYID = FACULTY.ID and
                COURSE.PRINTONSCHEDULE = 1 and
                not exists (select ID from dbo.CLASSMEETINGTEMPLATE where CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID)

    --set faculty/term group specific column saying if grid and no meetings grid is visible

    --not visible when there are no classes with meetings   

    update @SCHEDULE
        set  
            HIDEGRID =
                case when (select count(1) 
                    from @SCHEDULE as LOOK 
                    where 
                        LOOK.FACULTYID = SCHEDULE.FACULTYID and 
                        LOOK.TERMID = SCHEDULE.TERMID and
                        not LOOK.CYCLEDAYID is null
                    ) = 0 then 1 else 0 end,
            HIDENOMEETINGSGRID =    
                case when (select count(1) 
                    from @SCHEDULE as LOOK 
                    where 
                        LOOK.FACULTYID = SCHEDULE.FACULTYID and 
                        LOOK.TERMID = SCHEDULE.TERMID and
                        LOOK.CYCLEDAYID is null
                    ) = 0 then 1 else 0 end
    from @SCHEDULE as SCHEDULE  

    select 
        'http://www.blackbaud.com?FACULTYID=' + CONVERT(nvarchar(36), FACULTYID) as FACULTYID, 
        FACULTY.NAME as FACULTYNAME, 
        FACULTY.KEYNAME as FACULTY_KEYNAME,
        TERM.ID as TERMID,
        TERMNAMECODE.DESCRIPTION as TERM, 
        TERM.STARTDATE as TERM_STARTDATE,
        ACADEMICYEAR,
        CYCLEDAYCOUNT,
        CYCLEDAY.CODE as CYCLEDAYCODE, 
        CYCLEDAY.SEQUENCE as CYCLEDAY_SEQUENCE,
        PERIOD,
        PERIOD_SEQUENCE, --start time

        'http://www.blackbaud.com?CLASSID=' + CONVERT(nvarchar(36), CLASSID) as CLASSID,
        LENGTHINPERIODS,
        PERIODCOUNT,
        STARTTIME as INTERVAL_STARTTIME,
        LENGTHIN_TIMEROWS as LENGTHIN_TIMEROWS,
        TIMEROW_COUNT as TIMEROW_COUNT,
        SCHEDULE.NAME,
        CLASSNAME, 
        case 
            when (@BREAKDOWNBY = 1 and not PERIOD is null) then
                PERIOD + ' ' +
                dbo.fnFormatDateTime(CLASS_STARTTIME,'HH:MM 12') + ' - ' + 
                dbo.fnFormatDateTime(CLASS_ENDTIME,'HH:MM 12')
            else 
                dbo.fnFormatDateTime(CLASS_STARTTIME,'HH:MM 12') + ' - ' + 
                dbo.fnFormatDateTime(CLASS_ENDTIME,'HH:MM 12') end as TIMES,
        HIDEGRID,
        HIDENOMEETINGSGRID,
        @INCLUDENOMEETINGS as INCLUDENOMEETINGS,
        @DISPLAYBY as DISPLAYBY,
        @INTERVAL as INTERVAL,
        @BREAKDOWNBY as BREAKDOWNBY 
    from @SCHEDULE as SCHEDULE
        inner join dbo.CONSTITUENT FACULTY 
            on FACULTY.ID = SCHEDULE.FACULTYID
        inner join dbo.TERM 
            on TERM.ID = SCHEDULE.TERMID
        inner join dbo.TERMNAMECODE 
            on TERMNAMECODE.ID = TERM.TERMNAMECODEID
        left join dbo.CYCLEDAY 
            on CYCLEDAY.ID = SCHEDULE.CYCLEDAYID
end