UFN_JOBOCCURRENCE_SCHEDULE

            CREATE function [dbo].[UFN_JOBOCCURRENCE_SCHEDULE](@FROM datetime, @TO datetime, @JOBOCCURRENCEID uniqueidentifier, @JOBID uniqueidentifier)
            returns @T table (ASSIGNMENTDATE datetime, OCCURRENCEID uniqueidentifier, JOBID uniqueidentifier, JOBSTARTTIME dbo.UDT_HOURMINUTE, JOBENDTIME dbo.UDT_HOURMINUTE, VOLUNTEERSNEEDED int, OPENINGS int)

            begin
                declare @tempdate datetime;

                --Adjust the start and end dates to fit within the occurrence's constraints if given

                declare @RECURRENCESTARTDATE datetime;
                declare @RECURRENCEENDDATE datetime;

                select
                    @RECURRENCESTARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(RECURRENCESTARTDATE),
                    @RECURRENCEENDDATE = dbo.UFN_DATE_GETEARLIESTTIME(RECURRENCEENDDATE)
                from dbo.JOBOCCURRENCE
                where
                    ID = @JOBOCCURRENCEID

                if @RECURRENCESTARTDATE > @FROM
                    set @FROM = @RECURRENCESTARTDATE
                if @RECURRENCEENDDATE < @TO
                    set @TO = @RECURRENCEENDDATE

                --Create work tables

                declare @day table (dy datetime);

                --populate day

                set @tempdate = dbo.[UFN_DATE_GETEARLIESTTIME](@FROM);
                while @tempdate <= @TO and @tempdate < '99991231'
                begin
                    insert into @day(dy) values(@tempdate);
                    set @tempdate = dateadd(d, 1, @tempdate);
                end

                insert into @T(ASSIGNMENTDATE, OCCURRENCEID, JOBID, JOBSTARTTIME, JOBENDTIME, VOLUNTEERSNEEDED, OPENINGS)
                select dytbl.DY assignmentdate, JO.ID, JO.JOBID, 
                        JO.STARTTIME, JO.ENDTIME,
                        JO.VOLUNTEERSNEEDED, JO.VOLUNTEERSNEEDED - count(VA.ID)
                from JOBOCCURRENCE JO
                inner join @day dytbl
                    on dbo.UFN_JOBSCHEDULE_DATEVALID2(dytbl.DY,JO.TYPECODE,JO.STARTDATE,JO.RECURRENCESTARTDATE,JO.ENDDATE,JO.RECURRENCEENDDATE) = 1
                        and dbo.UFN_VOLUNTEER_DAYOFWEEKMATCHES(JO.DAYOFWEEKCODE, dytbl.DY) = 1
                left outer join VOLUNTEERASSIGNMENT VA
                    on VA.JOBOCCURRENCEID = JO.ID
                        and dytbl.DY = VA.DATE
                where dytbl.DY between @FROM and @TO
                    and JO.ID = coalesce(@JOBOCCURRENCEID, JO.ID)
                    and JO.JOBID = coalesce(@JOBID, JO.JOBID)
                group by dytbl.DY, JO.ID, JO.JOBID, JO.STARTTIME, JO.ENDTIME, JO.VOLUNTEERSNEEDED;

                return;
            end