USP_DATALIST_JOBOCCURRENCESCHEDULE

This datalist returns all dates for an occurrence including assignments.

Parameters

Parameter	Parameter Type	Mode	Description
@JOBOCCURRENCEID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@FROM	datetime	IN	From
@TO	datetime	IN	To

Definition

Copy



            create procedure dbo.USP_DATALIST_JOBOCCURRENCESCHEDULE (@JOBOCCURRENCEID uniqueidentifier = null, @FROM datetime, @TO datetime)
            as
                set nocount on;

                select    coalesce(SCHEDULE.ASSIGNMENTDATE, VA.DATE),
                        JOB.NAME,
                        JOBOCCURRENCE.STARTTIME,
                        JOBOCCURRENCE.ENDTIME,
                        coalesce(SCHEDULE.VOLUNTEERSNEEDED, 0),
                        coalesce(SCHEDULE.OPENINGS, -1),
                        ltrim(replace(dbo.UDA_BUILDLIST(distinct '|' + CONSTITUENT.NAME), '|', char(10)))
                from [UFN_JOBOCCURRENCE_SCHEDULE](@FROM,@TO,@JOBOCCURRENCEID,null) SCHEDULE
                full join dbo.VOLUNTEERASSIGNMENT VA
                    on VA.JOBOCCURRENCEID = SCHEDULE.OCCURRENCEID
                        and SCHEDULE.ASSIGNMENTDATE = VA.DATE
                inner join dbo.JOBOCCURRENCE on JOBOCCURRENCE.ID = coalesce(VA.JOBOCCURRENCEID, SCHEDULE.OCCURRENCEID)
                inner join dbo.JOB on JOB.ID = JOBOCCURRENCE.JOBID
                left outer join dbo.CONSTITUENT
                    on VA.VOLUNTEERID = CONSTITUENT.ID
                where VA.DATE is null or VA.DATE between @FROM and @TO
                    and JOBOCCURRENCE.ID = @JOBOCCURRENCEID
                group by SCHEDULE.ASSIGNMENTDATE, JOB.NAME,
                        JOBOCCURRENCE.STARTTIME, JOBOCCURRENCE.ENDTIME,
                        SCHEDULE.VOLUNTEERSNEEDED, SCHEDULE.OPENINGS,
                        VA.DATE
                order by SCHEDULE.ASSIGNMENTDATE, JOB.NAME;