USP_SIMPLEDATALIST_SCHEDULEDPROGRAM_BYDATE

CREATE procedure dbo.USP_SIMPLEDATALIST_SCHEDULEDPROGRAM_BYDATE (
    @DATE datetime,  -- Currently being passed in as the client's datetime

    @EVENTDATE date,
    @SALESMETHODTYPECODE tinyint = 0
) as
    set nocount on;

    declare @SALESMETHODID uniqueidentifier = null;
    select @SALESMETHODID = [ID]
    from dbo.[SALESMETHOD]
    where [TYPECODE] = @SALESMETHODTYPECODE;

    -- Inlining on sales logic for performance

    declare @EVENTSONSALE table (
        ID uniqueidentifier,
        PROGRAMID uniqueidentifier,
        PROGRAMNAME nvarchar(100)
    );

    insert into @EVENTSONSALE
    select
        EVENT.ID,
        PROGRAM.ID,
        PROGRAM.NAME
    from
        dbo.EVENT
    inner join
        dbo.PROGRAM on PROGRAM.ID = EVENT.PROGRAMID
    inner join
        dbo.EVENTSALESMETHOD on EVENTSALESMETHOD.EVENTID = EVENT.ID and EVENTSALESMETHOD.SALESMETHODID = @SALESMETHODID
    left outer join
        dbo.PROGRAMSALESMETHOD on PROGRAMSALESMETHOD.PROGRAMID = EVENT.PROGRAMID and PROGRAMSALESMETHOD.SALESMETHODID = @SALESMETHODID
    where
        EVENT.STARTDATE = @EVENTDATE
        and EVENT.ISACTIVE = 1
        and PROGRAM.ISACTIVE = 1
        and (
            @DATE >= EVENTSALESMETHOD.ONSALEDATETIME
            and (
                ((PROGRAMSALESMETHOD.ONSALEENDTYPECODE is null or PROGRAMSALESMETHOD.ONSALEENDTYPECODE = 0) and @DATE < EVENT.STARTDATETIME)  -- Event start time

                or (PROGRAMSALESMETHOD.ONSALEENDTYPECODE = 1 and -PROGRAMSALESMETHOD.ONSALEENDINTERVAL > datediff(minute, EVENT.STARTDATETIME, @DATE))  -- Minutes before start

                or (PROGRAMSALESMETHOD.ONSALEENDTYPECODE = 2 and cast(-PROGRAMSALESMETHOD.ONSALEENDINTERVAL as bigint) * 60 > datediff(minute, EVENT.STARTDATETIME, @DATE))  -- Hours before start

                or (PROGRAMSALESMETHOD.ONSALEENDTYPECODE = 3 and PROGRAMSALESMETHOD.ONSALEENDINTERVAL > datediff(minute, EVENT.STARTDATETIME, @DATE))  -- Minutes after start

            )
        );

    -- Doing a separate join on the ticket counts view after events have been

    -- filtered by date keeps from having sales order item tables scanned

    select distinct
        EVENTSONSALE.PROGRAMID as VALUE,
        EVENTSONSALE.PROGRAMNAME as LABEL
    from
        @EVENTSONSALE as EVENTSONSALE
    inner join
        dbo.V_PROGRAMEVENT_TICKETCOUNTS as TICKETCOUNTS on TICKETCOUNTS.ID = EVENTSONSALE.ID
    where
        TICKETCOUNTS.AVAILABILITY > 0
    order by
        EVENTSONSALE.PROGRAMNAME;

    return 0;