USP_DATALIST_VOLUNTEERSCHEDULE

This datalist returns all assignments for a volunteer.

Parameters

Parameter	Parameter Type	Mode	Description
@VOLUNTEERID	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_VOLUNTEERSCHEDULE (@VOLUNTEERID uniqueidentifier = null, @FROM datetime, @TO datetime)
            as
                set nocount on;

                select    VOLUNTEERASSIGNMENT.DATE,
                        JOB.NAME,
                        JOBOCCURRENCE.STARTTIME,
                        JOBOCCURRENCE.ENDTIME,
                        ltrim(replace(dbo.UDA_BUILDLIST(distinct '|' + CONSTITUENT.NAME), '|', char(10))),
                        count(CONSTITUENT.ID),
                        JOBOCCURRENCE.ID,
                        JOBOCCURRENCE.VOLUNTEERSNEEDED
                from dbo.VOLUNTEERASSIGNMENT
                INNER JOIN dbo.JOBOCCURRENCE 
                    on dbo.VOLUNTEERASSIGNMENT.JOBOCCURRENCEID = dbo.JOBOCCURRENCE.ID
                inner join dbo.JOB
                    on dbo.JOBOCCURRENCE.JOBID = dbo.JOB.ID
                left outer join dbo.VOLUNTEERASSIGNMENT VA
                    on VA.JOBOCCURRENCEID = JOBOCCURRENCE.ID
                        and VOLUNTEERASSIGNMENT.DATE = VA.DATE
                left outer join dbo.CONSTITUENT
                    on VA.VOLUNTEERID = CONSTITUENT.ID
                where VOLUNTEERASSIGNMENT.VOLUNTEERID = @VOLUNTEERID
                        and VOLUNTEERASSIGNMENT.DATE between @FROM and @TO
                group by JOB.NAME,
                        VOLUNTEERASSIGNMENT.DATE,
                        JOBOCCURRENCE.STARTTIME,
                        JOBOCCURRENCE.ENDTIME,
                        JOBOCCURRENCE.ID,
                        JOBOCCURRENCE.VOLUNTEERSNEEDED
                order by VOLUNTEERASSIGNMENT.DATE;