USP_SIMPLEDATALIST_PROGRAMEVENTBYDATE

Lists programs that have events on a specific date.
Parameters

Parameter	Parameter Type	Mode	Description
@STARTDATE	datetime	IN	STARTDATE
@TYPECODE	tinyint	IN	TYPECODE
@ITINERARYITEMID	uniqueidentifier	IN	ITINERARYITEMID
Definition

Copy


              CREATE procedure dbo.USP_SIMPLEDATALIST_PROGRAMEVENTBYDATE
             ( @STARTDATE datetime,
                @TYPECODE tinyint,
                @ITINERARYITEMID uniqueidentifier = null)
              as
              set nocount on;

                if @ITINERARYITEMID is null
                begin
                    select 
                      PROGRAM.ID as VALUE, 
                      PROGRAM.NAME as LABEL
                    from dbo.PROGRAM
                    inner join dbo.EVENT on PROGRAM.ID = EVENT.PROGRAMID
                    inner join dbo.EVENTSALESMETHOD on EVENT.ID = EVENTSALESMETHOD.EVENTID
                    inner join dbo.SALESMETHOD on EVENTSALESMETHOD.SALESMETHODID = SALESMETHOD.ID
                    where SALESMETHOD.TYPECODE = @TYPECODE and 
                        dbo.UFN_DATE_GETEARLIESTTIME(EVENT.STARTDATE) = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE) and
                        PROGRAM.ISACTIVE = 1 and
                        PROGRAM.ISPREREGISTERED = 0
                    group by PROGRAM.ID, PROGRAM.NAME
                end
                else
                begin
                    declare @PROGRAMID uniqueidentifier;
                    select @PROGRAMID = E.PROGRAMID
                    from dbo.ITINERARYITEM
                    inner join dbo.EVENT E on E.ID = ITINERARYITEM.EVENTID
                    where ITINERARYITEM.ID = @ITINERARYITEMID
                        and ITEMTYPECODE = 0

                    select 
                      PROGRAM.ID as VALUE, 
                      PROGRAM.NAME as LABEL
                    from dbo.PROGRAM
                    inner join dbo.EVENT on PROGRAM.ID = EVENT.PROGRAMID
                    inner join dbo.EVENTSALESMETHOD on EVENT.ID = EVENTSALESMETHOD.EVENTID
                    inner join dbo.SALESMETHOD on EVENTSALESMETHOD.SALESMETHODID = SALESMETHOD.ID
                    where SALESMETHOD.TYPECODE = @TYPECODE and 
                        dbo.UFN_DATE_GETEARLIESTTIME(EVENT.STARTDATE) = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE) and
                        (PROGRAM.ISACTIVE = 1 or PROGRAM.ID = @PROGRAMID) and
                        PROGRAM.ISPREREGISTERED = 0
                    group by PROGRAM.ID, PROGRAM.NAME
                end