UFN_VOLUNTEER_AVAILABLESPECIFICDAYS_2

CREATE function [dbo].[UFN_VOLUNTEER_AVAILABLESPECIFICDAYS_2](@DAYSXML xml, @VOLUNTEERID uniqueidentifier, @CURRENTDATE datetime)
returns table as return

select 
    T.c.value('(DATE)[1]', 'Date') AVAILABLEDATE, 
    VOLUNTEERAVAILABILITY.VOLUNTEERID, 
    STARTTIME, 
    ENDTIME
from 
    dbo.VOLUNTEERAVAILABILITY
    inner join @DAYSXML.nodes('ITEM') T(c) on
        dbo.UFN_SCHEDULE_DATEVALID2(T.c.value('(DATE)[1]', 'Date'), VOLUNTEERAVAILABILITY.FULLSTARTDATE, VOLUNTEERAVAILABILITY.FULLENDDATE) = 1
        and dbo.UFN_VOLUNTEER_DAYOFWEEKMATCHES(VOLUNTEERAVAILABILITY.DAYOFWEEKCODE, T.c.value('(DATE)[1]', 'Date')) = 1
    inner join dbo.VOLUNTEERDATERANGE on  VOLUNTEERDATERANGE.CONSTITUENTID = VOLUNTEERAVAILABILITY.VOLUNTEERID
    left join dbo.VOLUNTEER on VOLUNTEER.ID = VOLUNTEERDATERANGE.CONSTITUENTID
where 
    (VOLUNTEERAVAILABILITY.VOLUNTEERID = @VOLUNTEERID or @VOLUNTEERID is null)
    and (cast(VOLUNTEERDATERANGE.DATEFROM as DATE) <= cast(@CURRENTDATE as DATE) or VOLUNTEERDATERANGE.DATEFROM is null)
    and (cast(VOLUNTEERDATERANGE.DATETO as DATE) >= cast(@CURRENTDATE as DATE) or VOLUNTEERDATERANGE.DATETO is null)
    and ((T.c.value('(DATE)[1]', 'Date') < VOLUNTEER.UNAVAILABLEFROM or VOLUNTEER.UNAVAILABLEFROM is null)
        or (T.c.value('(DATE)[1]', 'Date') > VOLUNTEER.UNAVAILABLETO or VOLUNTEER.UNAVAILABLETO is null ))

union

select 
    VOLUNTEERAVAILABLE.AVAILABLEDATE, 
    VOLUNTEERAVAILABLE.ID, 
    '', 
    ''
from 
    (
        select 
            T.c.value('(DATE)[1]', 'Date') AVAILABLEDATE, 
            CONSTITUENT.ID
        from dbo.CONSTITUENT 
            inner join dbo.VOLUNTEER on VOLUNTEER.ID = CONSTITUENT.ID
            cross join @DAYSXML.nodes('ITEM') T(c)
        where (@VOLUNTEERID is null or (@VOLUNTEERID is not null and CONSTITUENT.ID = @VOLUNTEERID))
            and (
                (UNAVAILABLEFROM is null or (UNAVAILABLEFROM is not null and T.c.value('(DATE)[1]', 'Date') < UNAVAILABLEFROM))
                or (UNAVAILABLETO is null or (UNAVAILABLETO is not null and T.c.value('(DATE)[1]', 'Date') > UNAVAILABLETO))
            )
    ) VOLUNTEERAVAILABLE
    inner join dbo.VOLUNTEERDATERANGE on  VOLUNTEERDATERANGE.CONSTITUENTID = VOLUNTEERAVAILABLE.ID
    left join dbo.VOLUNTEER on VOLUNTEER.ID = VOLUNTEERDATERANGE.CONSTITUENTID
    left outer join dbo.VOLUNTEERAVAILABILITY on VOLUNTEERAVAILABILITY.VOLUNTEERID = VOLUNTEERAVAILABLE.ID
where VOLUNTEERAVAILABILITY.VOLUNTEERID is null
    and (cast(VOLUNTEERDATERANGE.DATEFROM as DATE) <= cast(@CURRENTDATE as DATE) or VOLUNTEERDATERANGE.DATEFROM is null)
    and (cast(VOLUNTEERDATERANGE.DATETO as DATE) >= cast(@CURRENTDATE as DATE) or VOLUNTEERDATERANGE.DATETO is null)
    and ((VOLUNTEERAVAILABLE.AVAILABLEDATE < VOLUNTEER.UNAVAILABLEFROM or VOLUNTEER.UNAVAILABLEFROM is null)
        or (VOLUNTEERAVAILABLE.AVAILABLEDATE > VOLUNTEER.UNAVAILABLETO or VOLUNTEER.UNAVAILABLETO is null ))