USP_SIMPLEDATALIST_TERM_GROUPEDBYTIME

CREATE procedure dbo.USP_SIMPLEDATALIST_TERM_GROUPEDBYTIME
(
    @ACADEMICYEARID uniqueidentifier,
    @SESSIONID uniqueidentifier    -- used for the representative dates, does not necessarily imply school.

)
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 @SESSIONNAMECODEID uniqueidentifier

-- 2) Determine if the Session implies school

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

if @SCHOOLID is null
begin
    select @SCHOOLID = dbo.UFN_SESSION_GETSCHOOL_GROUPBYTIMEFILTER(@ACADEMICYEARID, @SESSIONID)
end

select 
    @STARTDATE = min(TERM.STARTDATE), 
    @ENDDATE = max(TERM.ENDDATE),
    @SESSIONNAMECODEID = SESSION.SESSIONNAMECODEID
from dbo.TERM
    inner join dbo.SESSION on TERM.SESSIONID = SESSION.ID
where SESSION.ID = @SESSIONID
group by SESSION.ID, SESSION.SESSIONNAMECODEID

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