USP_DATALIST_PROGRAMBYDATE

            CREATE procedure dbo.USP_DATALIST_PROGRAMBYDATE
            (
                @DATES xml
            )
            as
                set nocount on;    

                select 
                    PROGRAM.ID,
                    PROGRAM.NAME,
                    (
                        select 
                            EVENT.ID, 
                            EVENT.STARTDATE,
                            EVENT.NAME,
                            dbo.UFN_EVENT_GETAVAILABILITY(EVENT.ID) AVAILABILITY,
                            EVENT.STARTTIME, 
                            EVENT.ENDTIME,
                            EVENT.ENDDATE
                        from dbo.EVENT 
                        where EVENT.PROGRAMID = PROGRAM.ID and 
                        exists 
                        (
                            select 
                                STARTDATE 
                            from @DATES.nodes('/DATES/ITEM') T(c)
                            where 
                            EVENT.STARTDATE >= T.c.value('(STARTDATE)[1]','datetime')
                            and EVENT.STARTDATE <= T.c.value('(ENDDATE)[1]','datetime') 
                        )
                        for xml raw('ITEM'),type,elements,root('EVENTS'),BINARY BASE64
                    ) as EVENTS
                from dbo.PROGRAM
                where
                    PROGRAM.ISACTIVE = 1 and
                    exists (select ID from dbo.PROGRAMPRICE where PROGRAMPRICE.PROGRAMID = PROGRAM.ID) and
                    exists
                    (
                        select
                            ID
                        from dbo.EVENT
                        where EVENT.PROGRAMID = PROGRAM.ID and
                        exists
                        (
                            select 
                                STARTDATE 
                            from @DATES.nodes('/DATES/ITEM') T(c)
                            where 
                            EVENT.STARTDATE >= T.c.value('(STARTDATE)[1]','datetime')
                            and EVENT.STARTDATE <= T.c.value('(ENDDATE)[1]','datetime') 
                        )
                    )