USP_SIMPLEDATALIST_ACADEMICYEAR_GROUPEDBYTIME

create procedure dbo.USP_SIMPLEDATALIST_ACADEMICYEAR_GROUPEDBYTIME as
select
    T.ID as VALUE,
    ACADEMICYEARNAMECODE.DESCRIPTION +
        case when T.COLAPSE = 0
            then ' - ' + CONSTITUENT.NAME
            else ''
        end as LABEL
from
    (select
        ACADEMICYEAR.ID, 
        ACADEMICYEAR.ACADEMICYEARNAMECODEID,
        ACADEMICYEAR.STARTDATE,
        ACADEMICYEAR.SCHOOLID,
        case when (max(ACADEMICYEAR.STARTDATE) over (partition by ACADEMICYEAR.ACADEMICYEARNAMECODEID) != min(ACADEMICYEAR.STARTDATE) over (partition by ACADEMICYEAR.ACADEMICYEARNAMECODEID)
            or max(ACADEMICYEAR.ENDDATE) over (partition by ACADEMICYEAR.ACADEMICYEARNAMECODEID) != min(ACADEMICYEAR.ENDDATE) over (partition by ACADEMICYEAR.ACADEMICYEARNAMECODEID))
            then 0
            else 1
        end as COLAPSE,
        ROW_NUMBER() over (partition by ACADEMICYEAR.ACADEMICYEARNAMECODEID order by ACADEMICYEAR.STARTDATE) as ROW
    from dbo.ACADEMICYEAR) T
    inner join dbo.ACADEMICYEARNAMECODE on T.ACADEMICYEARNAMECODEID = ACADEMICYEARNAMECODE.ID
    inner join dbo.CONSTITUENT on T.SCHOOLID = CONSTITUENT.ID
where T.COLAPSE = 0 or T.ROW = 1
order by T.STARTDATE