USP_KPI_PROGRAM_DAILYATTENDANCE_VALUE

                    CREATE procedure dbo.USP_KPI_PROGRAM_DAILYATTENDANCE_VALUE
                    (
                        @VALUE integer output, 
                        @ASOFDATE datetime, 
                        @PROGRAMSELECTIONID uniqueidentifier = null,
                        @PROGRAMCATEGORYCODEID uniqueidentifier = null,
                        @DATEFRAMENUMBER integer,
                        @DATEFRAMEINTERVAL tinyint,
                        @EVENTTIMEFRAMESTART dbo.UDT_HOURMINUTE,
                        @EVENTTIMEFRAMEEND dbo.UDT_HOURMINUTE,
                        @INCLUDEDAILYADMISSION bit,
                        @INCLUDESCHEDULEDEVENT bit
                    )
                    as
                        set nocount on;

                        declare @CURRENTDATE datetime = dbo.UFN_DATE_GETLATESTTIME(getdate())
                        if @ASOFDATE is not null
                            set @CURRENTDATE = dbo.UFN_DATE_GETLATESTTIME(@ASOFDATE)

                        declare @STARTDATE datetime = null
                        set @STARTDATE = case @DATEFRAMEINTERVAL
                            when 0 then dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day, -1 * @DATEFRAMENUMBER, @CURRENTDATE))
                            when 1 then dbo.UFN_DATE_GETEARLIESTTIME(dateadd(week, -1 * @DATEFRAMENUMBER, @CURRENTDATE))
                            when 2 then dbo.UFN_DATE_GETEARLIESTTIME(dateadd(month, -1 * @DATEFRAMENUMBER, @CURRENTDATE))
                            when 3 then dbo.UFN_DATE_GETEARLIESTTIME(dateadd(year, -1 * @DATEFRAMENUMBER, @CURRENTDATE))
                        end

                        --Attendance

                        select @VALUE = coalesce(sum([SALESORDERITEM].[QUANTITY]),0)
                        from dbo.[SALESORDERITEMTICKET]
                        inner join dbo.[SALESORDERITEM]
                            on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEM].[ID]
                        inner join dbo.[SALESORDER]
                            on [SALESORDERITEM].[SALESORDERID] = [SALESORDER].[ID]
                        left join dbo.[EVENT]
                            on [SALESORDERITEMTICKET].[EVENTID] = [EVENT].[ID]
                        left join dbo.[SALESORDERITEMITEMDISCOUNT]
                            on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEMITEMDISCOUNT].[SALESORDERITEMID]
                        inner join dbo.[PROGRAM]
                            on [SALESORDERITEMTICKET].[PROGRAMID] = [PROGRAM].[ID] or
                                [EVENT].[PROGRAMID] = [PROGRAM].[ID]
                        left join dbo.[RESERVATION]
                            on [SALESORDER].[ID] = [RESERVATION].[ID]
                        where 
                            ( 
                                @PROGRAMSELECTIONID is null or
                                [PROGRAM].[ID] in (select [ID] from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@PROGRAMSELECTIONID))
                            ) and
                            (
                                @PROGRAMCATEGORYCODEID is null or
                                [PROGRAM].[PROGRAMCATEGORYCODEID] = @PROGRAMCATEGORYCODEID
                            ) and
                            (
                                (--Scheduled event

                                    [PROGRAM].[ISDAILYADMISSION] = 0 and
                                    @INCLUDESCHEDULEDEVENT = 1 and
                                    [EVENT].[STARTDATE] between @STARTDATE and @CURRENTDATE and
                                    [EVENT].[STARTTIME] between @EVENTTIMEFRAMESTART and @EVENTTIMEFRAMEEND
                                ) or
                                (--Daily admission

                                    [PROGRAM].[ISDAILYADMISSION] = 1 and
                                    @INCLUDEDAILYADMISSION = 1 and
                                    (
                                        (--Daily, Advance, Online sales

                                            [SALESORDER].[SALESMETHODTYPECODE] in (0,1,2) and
                                            [SALESORDER].[TRANSACTIONDATE] between @STARTDATE and @CURRENTDATE and
                                            [SALESORDER].[STATUSCODE] = 1
                                        ) or
                                        (--Group sales

                                            [SALESORDER].[SALESMETHODTYPECODE] = 3 and
                                            [RESERVATION].[ARRIVALDATE] between @STARTDATE and @CURRENTDATE and
                                            [SALESORDER].[STATUSCODE] in (1,3)
                                        )
                                    )
                                )
                            )