UFN_EVENT_ISONSALE_DATETIMEOFFSET

Returns whether the specified event is on-sale information for a given date with time zone awareness
Return

Return Type
bit
Parameters

Parameter	Parameter Type	Mode
@EVENTID	uniqueidentifier	IN
@SALEDATETIMEWITHOFFSET	datetimeoffset	IN
@SALESMETHODTYPECODE	tinyint	IN
Definition

Copy


        CREATE function dbo.UFN_EVENT_ISONSALE_DATETIMEOFFSET
        (
            @EVENTID uniqueidentifier,
            @SALEDATETIMEWITHOFFSET datetimeoffset,
            @SALESMETHODTYPECODE tinyint
        )
        returns bit
        with execute as caller
        as begin
            declare @ONSALE bit = 0            
            declare @ONSALEDATETIMEWITHOFFSET datetimeoffset
            declare @SALESMETHODID uniqueidentifier
            declare @SALEDATETIMECOMPAREWITHOFFSET datetimeoffset
            declare @OFFSALEDATETIMEWITHOFFSET datetimeoffset

            set @SALESMETHODID = dbo.UFN_SALESMETHOD_GETIDFROMTYPECODE(@SALESMETHODTYPECODE)

            select
                @ONSALEDATETIMEWITHOFFSET = EVENTSALESMETHOD.ONSALEDATETIMEWITHOFFSET,
                @OFFSALEDATETIMEWITHOFFSET = case PROGRAMSALESMETHOD.ONSALEENDTYPECODE 
                            when 0 then [EVENT].[STARTDATETIMEWITHOFFSET]
                            when 1 then dateadd(mi, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET])
                            when 2 then dateadd(hh, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET])
                            when 3 then dateadd(mi, [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET])
                            end
            from
                dbo.EVENTSALESMETHOD
            inner join dbo.EVENT on EVENTSALESMETHOD.EVENTID = EVENT.ID
            left join dbo.PROGRAMSALESMETHOD on 
                EVENTSALESMETHOD.SALESMETHODID    = PROGRAMSALESMETHOD.SALESMETHODID and
                EVENT.PROGRAMID = PROGRAMSALESMETHOD.PROGRAMID
            where
                EVENTSALESMETHOD.SALESMETHODID = @SALESMETHODID and
                EVENTID = @EVENTID and
                (PROGRAMSALESMETHOD.SALESMETHODID is null or PROGRAMSALESMETHOD.SALESMETHODID = @SALESMETHODID)


            if @ONSALEDATETIMEWITHOFFSET is not null
            begin
                set @SALEDATETIMECOMPAREWITHOFFSET = @ONSALEDATETIMEWITHOFFSET;

                if @OFFSALEDATETIMEWITHOFFSET is null 
                    set @OFFSALEDATETIMEWITHOFFSET = @SALEDATETIMEWITHOFFSET;

                if @SALEDATETIMECOMPAREWITHOFFSET <= @SALEDATETIMEWITHOFFSET and @SALEDATETIMEWITHOFFSET <= @OFFSALEDATETIMEWITHOFFSET
                    set @ONSALE = 1
            end

            return @ONSALE
        end