USP_SIMPLEDATALIST_RESCHEDULETICKETSAVAILABLEEVENTS

CREATE procedure dbo.USP_SIMPLEDATALIST_RESCHEDULETICKETSAVAILABLEEVENTS
(
    @EVENTID uniqueidentifier = null,
    @PRICETYPECODEID uniqueidentifier = null
)
as begin

    if @EVENTID is null
    begin
        select null VALUE, null LABEL;
        return;
    end;

    with UPCOMINGEVENTS_CTE as (
        select top 5
            UPCOMINGEVENT.ID as VALUE,
            dbo.UFN_TRANSLATIONFUNCTION_RESCHEDULETICKETSAVAILABLEEVENT(UPCOMINGEVENT.ID) as LABEL
        from dbo.[EVENT]
            inner join dbo.[EVENT] UPCOMINGEVENT on (UPCOMINGEVENT.PROGRAMID = [EVENT].PROGRAMID and UPCOMINGEVENT.ID <> [EVENT].ID)
            cross apply dbo.UFN_PROGRAM_GETEVENTSANDPRICETYPES([EVENT].PROGRAMID, 0) PRICETYPES
        where UPCOMINGEVENT.STARTDATETIMEWITHOFFSET >= sysdatetimeoffset()  -- SQL Server knows how to correctly compare datetimeoffsets with different offsets

            and [EVENT].ID = @EVENTID
            and PRICETYPES.EVENTID = UPCOMINGEVENT.ID
            and PRICETYPES.PRICETYPECODEID = @PRICETYPECODEID
        order by UPCOMINGEVENT.STARTDATETIMEWITHOFFSET
    )
    select
        @EVENTID as VALUE,
        dbo.UFN_TRANSLATIONFUNCTION_RESCHEDULETICKETSAVAILABLEEVENT(@EVENTID) as LABEL

    union all

    select
        '00000000-0000-0000-0000-000000000000' as VALUE,
        case when (select count(*) from UPCOMINGEVENTS_CTE) = 0 then
            case 
                when not exists (
                    select 1
                    from dbo.[EVENT] GIVENEVENT
                        inner join dbo.[EVENT] OTHEREVENTSFORPROGRAM on OTHEREVENTSFORPROGRAM.PROGRAMID = GIVENEVENT.PROGRAMID and OTHEREVENTSFORPROGRAM.ID <> GIVENEVENT.ID
                    where GIVENEVENT.ID = @EVENTID
                )
                    then 'NOMOREEVENTS'
                when (
                        select ACTIVE 
                        from dbo.PRICETYPECODE 
                        where ID = @PRICETYPECODEID
                    ) = 0
                    or not exists(
                        select * from dbo.EVENT 
                            inner join dbo.PROGRAMPRICE on PROGRAMPRICE.PROGRAMID = EVENT.PROGRAMID
                        where event.ID=@EVENTID
                            and PROGRAMPRICE.PRICETYPECODEID = @PRICETYPECODEID
                    )
                    then 'UNAVAILABLEPRICETYPE'
            end
        else
            'SUCCESS'
        end as LABEL

    union all

    select VALUE, LABEL from UPCOMINGEVENTS_CTE;
end