UFN_VOLUNTEER_AVAILABLESPECIFICDAYS

            CREATE function [dbo].[UFN_VOLUNTEER_AVAILABLESPECIFICDAYS](@DAYLIST nvarchar(max), @VOLUNTEERID uniqueidentifier, @ALLDAYS bit)
            returns @T table (AVAILABLEDATE datetime, VOLUNTEERID uniqueidentifier, STARTTIME dbo.UDT_HOURMINUTE, ENDTIME dbo.UDT_HOURMINUTE)

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

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

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

                --create all values table

                declare @results table (AVAILABLEDATE datetime, VOLUNTEERID uniqueidentifier, STARTTIME dbo.UDT_HOURMINUTE, ENDTIME dbo.UDT_HOURMINUTE)


                --Create work tables

                declare @DAY table (DY datetime);

                --populate day

                declare @DATE nvarchar(8)
                declare @DATECOUNT int;

                Set @DAYLIST = ltrim(rtrim(@DAYLIST))
                set @DATECOUNT = 0
                while len(@DAYLIST) >= 8
                begin
                    set @DATE = left(@DAYLIST, 8)
                    insert into @DAY(DY) values(cast(@DATE as datetime));
                    set @DATECOUNT = @DATECOUNT + 1
                    if len(@DAYLIST) > 8
                        set @DAYLIST = right(@DAYLIST, len(@DAYLIST) - 8)
                    else
                        set @DAYLIST = ''
                end

                insert into @results(AVAILABLEDATE, VOLUNTEERID, STARTTIME, ENDTIME)
                select DYTBL.DY, VA.VOLUNTEERID, STARTTIME, ENDTIME
                from dbo.VOLUNTEERAVAILABILITY VA
                inner join @DAY DYTBL on
                    dbo.UFN_SCHEDULE_DATEVALID2(dytbl.DY, VA.FULLSTARTDATE,VA.FULLENDDATE) = 1
                        and dbo.UFN_VOLUNTEER_DAYOFWEEKMATCHES(VA.DAYOFWEEKCODE, DYTBL.DY) = 1
                where VA.VOLUNTEERID = coalesce(@VOLUNTEERID, VA.VOLUNTEERID)
                union
                select VOLAVAIL.DY, VOLAVAIL.ID, '', ''
                from 
                    (select dytbl.DY, CONSTITUENT.ID
                    from dbo.CONSTITUENT 
                            inner join dbo.VOLUNTEER on VOLUNTEER.ID = CONSTITUENT.ID,
                            @day dytbl
                    where CONSTITUENT.ID = coalesce(@VOLUNTEERID, CONSTITUENT.ID)
                        and (dytbl.DY < coalesce(UNAVAILABLEFROM, '01/01/9999')
                        or dytbl.DY > coalesce(UNAVAILABLETO, '01/01/1753'))
                    ) VOLAVAIL
                left outer join dbo.VOLUNTEERAVAILABILITY VA
                    on VA.VOLUNTEERID = VOLAVAIL.ID
                where VA.VOLUNTEERID is null;

                insert into @T(AVAILABLEDATE, VOLUNTEERID, STARTTIME, ENDTIME)
                select AVAILABLEDATE, VOLUNTEERID, STARTTIME, ENDTIME
                from @results as R
                inner join dbo.VOLUNTEERDATERANGE on VOLUNTEERDATERANGE.CONSTITUENTID = R.VOLUNTEERID
                left join dbo.VOLUNTEER on R.VOLUNTEERID = VOLUNTEER.ID
                where (VOLUNTEERDATERANGE.DATEFROM <= @UPPERBOUND or VOLUNTEERDATERANGE.DATEFROM is null)
                    and (VOLUNTEERDATERANGE.DATETO >= @LOWERBOUND or VOLUNTEERDATERANGE.DATETO is null)
                    and (AVAILABLEDATE < coalesce(UNAVAILABLEFROM, '01/01/9999')
                        or AVAILABLEDATE > coalesce(UNAVAILABLETO, '01/01/1753'));

                if @ALLDAYS = 1
                    with COUNT_CTE as (        
                        select VOLUNTEERID, count(1) as NUM
                        from @T
                        group by VOLUNTEERID
                    )
                    delete from @T
                    where exists (select 1 from COUNT_CTE where COUNT_CTE.VOLUNTEERID = VOLUNTEERID and NUM < @DATECOUNT)

                return;
            end