UFN_VOLUNTEER_GETVOLUNTEERSAVAILABLEFORDATERANGE

            CREATE function [dbo].[UFN_VOLUNTEER_GETVOLUNTEERSAVAILABLEFORDATERANGE]
            (
                @STARTDATETIME datetime, 
                @ENDDATETIME datetime,
                @VOLUNTEERTYPEID uniqueidentifier = null
            )
            returns @VOLUNTEERS table 
            (
                VOLUNTEERID uniqueidentifier,
                VALIDINTIME bit
            )

            begin
                declare @CURRENTDATE datetime;
                set @CURRENTDATE = getdate();

                declare @LOWERBOUND datetime;
                set @LOWERBOUND = dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE);

                declare @UPPERBOUND datetime;
                set @UPPERBOUND = dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE);


                declare @STARTTIME dbo.UDT_HOURMINUTE
                declare @ENDTIME dbo.UDT_HOURMINUTE
                set @STARTTIME = dbo.UFN_HOURMINUTE_GETFROMDATE(@STARTDATETIME)
                set @ENDTIME = dbo.UFN_HOURMINUTE_GETFROMDATE(@ENDDATETIME)

                --create all values table

                declare @RESULTS table 
                (
                    VOLUNTEERID uniqueidentifier,
                    VALIDINTIME bit
                );

                with MATCHINGDAYS_CTE as
                (
                    select 
                        VA.VOLUNTEERID as ID,
                        case 
                            when
                                dbo.UFN_SCHEDULE_DATEVALID2(@STARTDATETIME, VA.FULLSTARTDATE,VA.FULLENDDATE) = 1 and 
                                dbo.UFN_VOLUNTEER_DAYOFWEEKMATCHES(VA.DAYOFWEEKCODE, @STARTDATETIME) = 1 and
                                (
                                    (@STARTTIME >= VA.STARTTIME or VA.STARTTIME = '') and
                                    (@STARTTIME <= VA.ENDTIME or VA.ENDTIME = '')
                                )
                            then 1
                        else 0 end as STARTTIMEVALIDCOUNT,
                        case
                            when
                                dbo.UFN_SCHEDULE_DATEVALID2(@ENDDATETIME, VA.FULLSTARTDATE,VA.FULLENDDATE) = 1 and 
                                dbo.UFN_VOLUNTEER_DAYOFWEEKMATCHES(VA.DAYOFWEEKCODE, @ENDDATETIME) = 1 and
                                (
                                    (@ENDTIME >= VA.STARTTIME or VA.STARTTIME = '') and
                                    (@ENDTIME <= VA.ENDTIME or VA.ENDTIME = '')
                                )
                            then 1
                        else 0 end as ENDTIMEVALIDCOUNT
                    from dbo.VOLUNTEERAVAILABILITY VA
                )
                insert into @RESULTS (VOLUNTEERID, VALIDINTIME)
                select 
                    ID,
                    case
                        when sum(STARTTIMEVALIDCOUNT) > 0 and sum(ENDTIMEVALIDCOUNT) > 0 then 1
                        else 0
                    end as VALIDINTIME
                from MATCHINGDAYS_CTE 
                group by MATCHINGDAYS_CTE.ID

                insert into @RESULTS (VOLUNTEERID, VALIDINTIME)
                select 
                    VOLUNTEER.ID,
                    0
                from dbo.VOLUNTEER
                left outer join @RESULTS on
                    VOLUNTEER.ID = [@RESULTS].VOLUNTEERID
                where
                    [@RESULTS].VOLUNTEERID is null

                if @VOLUNTEERTYPEID is not null
                begin
                    delete from @RESULTS
                    where
                        [@RESULTS].VOLUNTEERID not in
                        (
                            select VOLUNTEERVOLUNTEERTYPE.VOLUNTEERID
                            from dbo.VOLUNTEERVOLUNTEERTYPE
                            where
                                VOLUNTEERVOLUNTEERTYPE.VOLUNTEERTYPEID = @VOLUNTEERTYPEID and
                                (
                                    (
                                        dbo.[UFN_DATE_FROMFUZZYDATE](VOLUNTEERVOLUNTEERTYPE.STARTDATE) is null or
                                        dbo.[UFN_DATE_FROMFUZZYDATE](VOLUNTEERVOLUNTEERTYPE.STARTDATE) <= @UPPERBOUND
                                    ) and
                                    (
                                        dbo.[UFN_DATE_FROMFUZZYDATE](VOLUNTEERVOLUNTEERTYPE.ENDDATE) is null or
                                        dbo.[UFN_DATE_FROMFUZZYDATE](VOLUNTEERVOLUNTEERTYPE.ENDDATE) >= @LOWERBOUND
                                    )
                                ) and
                                VOLUNTEERVOLUNTEERTYPE.STATUSCODE = 1
                        )                
                end

                insert into @VOLUNTEERS
                (
                    VOLUNTEERID,
                    VALIDINTIME
                )
                select  
                    VOLUNTEERID,
                    VALIDINTIME
                from @RESULTS
                where 
                    VOLUNTEERID in 
                    (
                        select 
                            VOLUNTEERID 
                        from @RESULTS
                        inner join dbo.VOLUNTEERDATERANGE on 
                            VOLUNTEERDATERANGE.CONSTITUENTID = VOLUNTEERID
                        left join dbo.VOLUNTEER on 
                            VOLUNTEERID = VOLUNTEER.ID
                        where 
                        (
                            VOLUNTEERDATERANGE.DATEFROM <= @UPPERBOUND or 
                            VOLUNTEERDATERANGE.DATEFROM is null
                        ) and 
                        (
                            VOLUNTEERDATERANGE.DATETO >= @LOWERBOUND or 
                            VOLUNTEERDATERANGE.DATETO is null
                        ) and 
                        (
                            (
                                @STARTDATETIME < coalesce(UNAVAILABLEFROM, cast('01/01/9999' as datetime)) or 
                                @STARTDATETIME > coalesce(UNAVAILABLETO, cast('01/01/1753' as datetime))
                            ) and
                            (
                                @ENDDATETIME < coalesce(UNAVAILABLEFROM, cast('01/01/9999' as datetime)) or 
                                @ENDDATETIME > coalesce(UNAVAILABLETO, cast('01/01/1753' as datetime))
                            )
                        )
                        group by VOLUNTEERID
                    );

                return;
            end