USP_SIMPLEDATALIST_SESSION_GROUPEDBYTIME

CREATE procedure dbo.USP_SIMPLEDATALIST_SESSION_GROUPEDBYTIME
(
    @ACADEMICYEARID uniqueidentifier
)
as

-- 1) Determine if the AY implies school

-- (If an AY was picked which was not the only one by that name, you know school was implied)

declare @SCHOOLID uniqueidentifier = dbo.UFN_ACADEMICYEAR_GETSCHOOL_GROUPBYTIMEFILTER(@ACADEMICYEARID)

declare @STARTDATE date
declare @ENDDATE date
declare @ACADEMICYEARNAMECODEID uniqueidentifier
select
    @STARTDATE = ACADEMICYEAR.STARTDATE,
    @ENDDATE = ACADEMICYEAR.ENDDATE,
    @ACADEMICYEARNAMECODEID = ACADEMICYEAR.ACADEMICYEARNAMECODEID
from dbo.ACADEMICYEAR
where ACADEMICYEAR.ID = @ACADEMICYEARID

select
    T.ID as VALUE,
    SESSIONNAMECODE.DESCRIPTION +
        case when T.COLAPSE = 0
            then ' - ' + CONSTITUENT.NAME
            else ''
        end as LABEL
from
    (
        select
            SESSION.ID,
            ACADEMICYEAR.SCHOOLID,
            SESSION.SESSIONNAMECODEID,
            SESSION.STARTDATE,
            case when (max(SESSION.STARTDATE) over (partition by SESSION.SESSIONNAMECODEID) != min(SESSION.STARTDATE) over (partition by SESSION.SESSIONNAMECODEID)
                or max(SESSION.ENDDATE) over (partition by SESSION.SESSIONNAMECODEID) != min(SESSION.ENDDATE) over (partition by SESSION.SESSIONNAMECODEID)
                or max(cast(TIMETABLE.CYCLEID as nvarchar(36))) over (partition by SESSION.SESSIONNAMECODEID) != min(cast(TIMETABLE.CYCLEID as nvarchar(36))) over (partition by SESSION.SESSIONNAMECODEID))
                then 0
                else 1
            end as COLAPSE,
            ROW_NUMBER() over (partition by SESSION.SESSIONNAMECODEID order by SESSION.STARTDATE) as ROW
        from 
            (
                select
                    SESSION.ID,
                    SESSION.SESSIONNAMECODEID,
                    SESSION.ACADEMICYEARID,
                    SESSION.TIMETABLEID,
                    min(TERM.STARTDATE) as STARTDATE,
                    max(TERM.ENDDATE) as ENDDATE
                from dbo.SESSION
                    inner join dbo.TERM on SESSION.ID = TERM.SESSIONID
                group by SESSION.ID, SESSION.SESSIONNAMECODEID, SESSION.ACADEMICYEARID, SESSION.TIMETABLEID
                having min(TERM.STARTDATE) >= @STARTDATE and max(TERM.ENDDATE) <= @ENDDATE
            ) [SESSION]
            inner join dbo.ACADEMICYEAR on SESSION.ACADEMICYEARID = ACADEMICYEAR.ID
                and ACADEMICYEAR.ACADEMICYEARNAMECODEID = @ACADEMICYEARNAMECODEID
                and (@SCHOOLID is null or ACADEMICYEAR.SCHOOLID = @SCHOOLID)
            inner join dbo.TIMETABLE on SESSION.TIMETABLEID = TIMETABLE.ID
    ) T
    inner join dbo.SESSIONNAMECODE on T.SESSIONNAMECODEID = SESSIONNAMECODE.ID
    inner join dbo.CONSTITUENT on T.SCHOOLID = CONSTITUENT.ID
where T.COLAPSE = 0 or T.ROW = 1
order by T.STARTDATE, SESSIONNAMECODE.DESCRIPTION