USP_DATALIST_ONLINEPROGRAMEVENTONSALE_BYPROGRAM_NOMAX

Parameters

Parameter	Parameter Type	Mode
@PROGRAMID	uniqueidentifier	IN
@STARTDATETIME	datetime	IN
@ENDDATETIME	datetime	IN
@INCLUDESOLDOUT	bit	IN
@NAME	nvarchar(150)	IN
@EXCLUDEDATE	date	IN
@LIMITROWS	int	IN
@EXCLUDEOFFSALE	bit	IN
Definition

Copy


            CREATE procedure dbo.USP_DATALIST_ONLINEPROGRAMEVENTONSALE_BYPROGRAM_NOMAX
            (
                @PROGRAMID uniqueidentifier = null,
                @STARTDATETIME datetime = null,
                @ENDDATETIME datetime = null,
                @INCLUDESOLDOUT bit = null,
                @NAME nvarchar(150) = null,
                @EXCLUDEDATE date = null,
                @LIMITROWS int = 0,
                @EXCLUDEOFFSALE bit = 0 --maintaining old behavior

            )
            as
            set nocount on;

            declare @ONLINESALESMETHOD uniqueidentifier = dbo.UFN_SALESMETHOD_GETIDFROMTYPECODE(2);--2 = online


            select 
                EVENTSONSALE.ID,
                EVENTSONSALE.NAME,
                EVENTSONSALE.AVAILABILITY,
                EVENTSONSALE.STARTDATE,
                EVENTSONSALE.STARTTIME,
                EVENTSONSALE.ENDTIME,
                EVENTSONSALE.URL,
                [EVENT].CAPACITY,
                case 
                    when [EVENT].SUPERCEDESPROGRAMPUBLICDESCRIPTION = 0 then isnull(PROGRAM.PUBLICDESCRIPTIONHTML , [EVENT].PUBLICDESCRIPTIONHTML)
                    else [EVENT].PUBLICDESCRIPTIONHTML 
                end PUBLICDESCRIPTION,
                [EVENT].STARTDATETIMEWITHOFFSET,
                case
                    when [PROGRAMSALESMETHOD].[ID] is null then [EVENT].[ENDDATETIMEWITHOFFSET]
                    when [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE] = 0 then [EVENT].[STARTDATETIMEWITHOFFSET]
                    when [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE] = 1 then dateadd(mi, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET])
                    when [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE] = 2 then dateadd(hh, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET])
                    when [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE] = 3 then dateadd(mi, [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET])
                end ENDDATETIMEWITHOFFSET
            from dbo.UFN_ONLINEPROGRAMEVENTONSALE_BYPROGRAM_2 (
                @PROGRAMID,
                @STARTDATETIME,
                @ENDDATETIME,
                @INCLUDESOLDOUT,
                @NAME,
                @EXCLUDEDATE,
                @LIMITROWS,
                @LIMITROWS,
                @EXCLUDEOFFSALE
            ) EVENTSONSALE
            inner join dbo.EVENT
                on EVENTSONSALE.ID = [EVENT].ID
            left outer join dbo.PROGRAM
                on [EVENT].PROGRAMID = PROGRAM.ID
            left join dbo.[PROGRAMSALESMETHOD]
                on PROGRAM.ID = [PROGRAMSALESMETHOD].[PROGRAMID]
                and    [PROGRAMSALESMETHOD].[SALESMETHODID] = @ONLINESALESMETHOD;