USP_DATALIST_RESERVATIONSTAFFASSIGNMENT
Lists staffing resources for a reservation along with who is assigned individually, or grouped if unassigned.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONTEXTID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_RESERVATIONSTAFFASSIGNMENT
(
@CONTEXTID uniqueidentifier
)
as
set nocount on;
declare @ITINERARYSTAFFRESOURCES table
(
ID uniqueidentifier,
QUANTITYNEEDED int,
ITINERARYNAME nvarchar(154),
VOLUNTEERTYPE nvarchar(100),
STARTTIME dbo.UDT_HOURMINUTE,
ENDTIME dbo.UDT_HOURMINUTE,
JOBOCCURRENCEID uniqueidentifier,
FILLEDBYCODE tinyint
)
insert into @ITINERARYSTAFFRESOURCES
select
ITINERARYSTAFFRESOURCE.ID as ID,
ITINERARYSTAFFRESOURCE.QUANTITYNEEDED as QUANTITYNEEDED,
ITINERARY.NAME,
VOLUNTEERTYPE.NAME,
dbo.[UFN_HOURMINUTE_GETFROMDATE](ITINERARY.STARTDATETIME) as STARTTIME,
dbo.[UFN_HOURMINUTE_GETFROMDATE](ITINERARY.ENDDATETIME) as ENDTIME,
ITINERARYSTAFFRESOURCE.JOBOCCURRENCEID,
ITINERARYSTAFFRESOURCE.FILLEDBYCODE
from dbo.ITINERARYSTAFFRESOURCE
inner join dbo.ITINERARY on
ITINERARYSTAFFRESOURCE.ITINERARYID = ITINERARY.ID
inner join dbo.VOLUNTEERTYPE on
ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
where
ITINERARY.RESERVATIONID = @CONTEXTID and
ITINERARY.STARTDATETIME is not null and
ITINERARY.ENDDATETIME is not null
declare @ITINERARYITEMSTAFFRESOURCES table
(
ID uniqueidentifier,
QUANTITYNEEDED int,
ITINERARYNAME nvarchar(154),
ITINERARYITEMNAME nvarchar(100),
VOLUNTEERTYPE nvarchar(100),
STARTTIME dbo.UDT_HOURMINUTE,
ENDTIME dbo.UDT_HOURMINUTE,
JOBOCCURRENCEID uniqueidentifier,
FILLEDBYCODE tinyint
)
insert into @ITINERARYITEMSTAFFRESOURCES
select
ITINERARYITEMSTAFFRESOURCE.ID as ID,
ITINERARYITEMSTAFFRESOURCE.QUANTITYNEEDED as QUANTITYNEEDED,
ITINERARY.NAME,
ITINERARYITEM.NAME,
VOLUNTEERTYPE.NAME,
ITINERARYITEM.STARTTIME,
ITINERARYITEM.ENDTIME,
ITINERARYITEMSTAFFRESOURCE.JOBOCCURRENCEID,
ITINERARYITEMSTAFFRESOURCE.FILLEDBYCODE
from dbo.ITINERARYITEMSTAFFRESOURCE
inner join dbo.ITINERARYITEM on
ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
inner join dbo.ITINERARY on
ITINERARYITEM.ITINERARYID = ITINERARY.ID
inner join dbo.VOLUNTEERTYPE on
ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
where
ITINERARY.RESERVATIONID = @CONTEXTID and
ITINERARYITEM.ITEMTYPECODE <> 3
declare @LIST table
(
ID uniqueidentifier,
ITINERARYNAME nvarchar(154),
ITINERARYITEMNAME nvarchar(100),
STARTTIME dbo.UDT_HOURMINUTE,
ENDTIME dbo.UDT_HOURMINUTE,
VOLUNTEERTYPE nvarchar(100),
ASSIGNEDTO nvarchar(154),
QUANTITYOPEN int,
ISSCHEDULED bit,
FILLEDBYCODE tinyint,
ISITINERARYLEVEL bit,
CONSTITUENTID uniqueidentifier
);
-- Select all staff and board type itinerary staff resources without assignments
with COUNT_CTE as
(
select
ITINERARYSTAFFRESOURCEASSIGNMENT.ITINERARYSTAFFRESOURCEID as ID,
count(1) as ASSIGNMENTCOUNT
from dbo.ITINERARYSTAFFRESOURCEASSIGNMENT
inner join @ITINERARYSTAFFRESOURCES as STAFFRESOURCES on
STAFFRESOURCES.ID = ITINERARYSTAFFRESOURCEASSIGNMENT.ITINERARYSTAFFRESOURCEID
group by ITINERARYSTAFFRESOURCEASSIGNMENT.ITINERARYSTAFFRESOURCEID
)
insert into @LIST
select
STAFFRESOURCES.ID as ID,
STAFFRESOURCES.ITINERARYNAME as ITINERARYNAME,
'' as ITINERARYITEMNAME,
STAFFRESOURCES.STARTTIME as STARTTIME,
STAFFRESOURCES.ENDTIME as ENDTIME,
STAFFRESOURCES.VOLUNTEERTYPE as VOLUNTEERTYPE,
'(Unassigned)' as ASSIGNEDTO,
STAFFRESOURCES.QUANTITYNEEDED - coalesce(COUNT.ASSIGNMENTCOUNT,0) as QUANTITYOPEN,
0 as ISSCHEDULED,
STAFFRESOURCES.FILLEDBYCODE,
1 as ISITINERARYLEVEL,
null as CONSTITUENTID
from @ITINERARYSTAFFRESOURCES as STAFFRESOURCES
left join COUNT_CTE as COUNT on
STAFFRESOURCES.ID = COUNT.ID
where
STAFFRESOURCES.FILLEDBYCODE <> 0 and
(
COUNT.ID is null or
STAFFRESOURCES.QUANTITYNEEDED <> COUNT.ASSIGNMENTCOUNT
);
-- Select all staff and board type itinerary item staff resources without assignments
with COUNT_CTE as
(
select
ITINERARYITEMSTAFFRESOURCEASSIGNMENT.ITINERARYITEMSTAFFRESOURCEID as ID,
count(1) as ASSIGNMENTCOUNT
from dbo.ITINERARYITEMSTAFFRESOURCEASSIGNMENT
inner join @ITINERARYITEMSTAFFRESOURCES as STAFFRESOURCES on
STAFFRESOURCES.ID = ITINERARYITEMSTAFFRESOURCEASSIGNMENT.ITINERARYITEMSTAFFRESOURCEID
group by ITINERARYITEMSTAFFRESOURCEASSIGNMENT.ITINERARYITEMSTAFFRESOURCEID
)
insert into @LIST
select
STAFFRESOURCES.ID as ID,
STAFFRESOURCES.ITINERARYNAME as ITINERARYNAME,
STAFFRESOURCES.ITINERARYITEMNAME as ITINERARYITEMNAME,
STAFFRESOURCES.STARTTIME as STARTTIME,
STAFFRESOURCES.ENDTIME as ENDTIME,
STAFFRESOURCES.VOLUNTEERTYPE as VOLUNTEERTYPE,
'(Unassigned)' as ASSIGNEDTO,
STAFFRESOURCES.QUANTITYNEEDED - coalesce(COUNT.ASSIGNMENTCOUNT,0) as QUANTITYOPEN,
0 as ISSCHEDULED,
STAFFRESOURCES.FILLEDBYCODE,
0 as ISITINERARYLEVEL,
null as CONSTITUENTID
from @ITINERARYITEMSTAFFRESOURCES as STAFFRESOURCES
left join COUNT_CTE as COUNT on
STAFFRESOURCES.ID = COUNT.ID
where
STAFFRESOURCES.FILLEDBYCODE <> 0 and
(
COUNT.ID is null or
STAFFRESOURCES.QUANTITYNEEDED <> COUNT.ASSIGNMENTCOUNT
)
-- Select all staff and board type itinerary staff resources with assignments
insert into @LIST
select
STAFFRESOURCES.ID as ID,
STAFFRESOURCES.ITINERARYNAME as ITINERARYNAME,
'' as ITINERARYITEMNAME,
STAFFRESOURCES.STARTTIME as STARTTIME,
STAFFRESOURCES.ENDTIME as ENDTIME,
STAFFRESOURCES.VOLUNTEERTYPE as VOLUNTEERTYPE,
dbo.UFN_CONSTITUENT_BUILDNAME(ITINERARYSTAFFRESOURCEASSIGNMENT.CONSTITUENTID) as ASSIGNEDTO,
null as QUANTITYOPEN,
1 as ISSCHEDULED,
STAFFRESOURCES.FILLEDBYCODE,
1 as ISITINERARYLEVEL,
ITINERARYSTAFFRESOURCEASSIGNMENT.CONSTITUENTID as CONSTITUENTID
from dbo.ITINERARYSTAFFRESOURCEASSIGNMENT
inner join @ITINERARYSTAFFRESOURCES as STAFFRESOURCES on
STAFFRESOURCES.ID = ITINERARYSTAFFRESOURCEASSIGNMENT.ITINERARYSTAFFRESOURCEID
where
STAFFRESOURCES.FILLEDBYCODE <> 0
-- Select all staff and board type itinerary item staff resources with assignments
insert into @LIST
select
STAFFRESOURCES.ID as ID,
STAFFRESOURCES.ITINERARYNAME as ITINERARYNAME,
STAFFRESOURCES.ITINERARYITEMNAME as ITINERARYITEMNAME,
STAFFRESOURCES.STARTTIME as STARTTIME,
STAFFRESOURCES.ENDTIME as ENDTIME,
STAFFRESOURCES.VOLUNTEERTYPE as VOLUNTEERTYPE,
dbo.UFN_CONSTITUENT_BUILDNAME(ITINERARYITEMSTAFFRESOURCEASSIGNMENT.CONSTITUENTID) as ASSIGNEDTO,
null as QUANTITYOPEN,
1 as ISSCHEDULED,
STAFFRESOURCES.FILLEDBYCODE,
0 as ISITINERARYLEVEL,
ITINERARYITEMSTAFFRESOURCEASSIGNMENT.CONSTITUENTID as CONSTITUENTID
from dbo.ITINERARYITEMSTAFFRESOURCEASSIGNMENT
inner join @ITINERARYITEMSTAFFRESOURCES as STAFFRESOURCES on
STAFFRESOURCES.ID = ITINERARYITEMSTAFFRESOURCEASSIGNMENT.ITINERARYITEMSTAFFRESOURCEID
where
STAFFRESOURCES.FILLEDBYCODE <> 0;
-- Select all volunteer type itinerary staff resources without assignments
with COUNT_CTE as
(
select
VOLUNTEERASSIGNMENT.JOBOCCURRENCEID as ID,
count(1) as ASSIGNMENTCOUNT
from dbo.VOLUNTEERASSIGNMENT
inner join @ITINERARYSTAFFRESOURCES as STAFFRESOURCES on
STAFFRESOURCES.JOBOCCURRENCEID = VOLUNTEERASSIGNMENT.JOBOCCURRENCEID
group by VOLUNTEERASSIGNMENT.JOBOCCURRENCEID
)
insert into @LIST
select
STAFFRESOURCES.ID as ID,
STAFFRESOURCES.ITINERARYNAME as ITINERARYNAME,
'' as ITINERARYITEMNAME,
STAFFRESOURCES.STARTTIME as STARTTIME,
STAFFRESOURCES.ENDTIME as ENDTIME,
STAFFRESOURCES.VOLUNTEERTYPE as VOLUNTEERTYPE,
'(Unassigned)' as ASSIGNEDTO,
STAFFRESOURCES.QUANTITYNEEDED - coalesce(COUNT.ASSIGNMENTCOUNT,0) as QUANTITYOPEN,
0 as ISSCHEDULED,
STAFFRESOURCES.FILLEDBYCODE,
1 as ISITINERARYLEVEL,
null as CONSTITUENTID
from @ITINERARYSTAFFRESOURCES as STAFFRESOURCES
left join COUNT_CTE as COUNT on
STAFFRESOURCES.JOBOCCURRENCEID = COUNT.ID
where
STAFFRESOURCES.FILLEDBYCODE = 0 and
(
COUNT.ID is null or
STAFFRESOURCES.QUANTITYNEEDED <> COUNT.ASSIGNMENTCOUNT
);
-- Select all volunteer type itinerary item staff resources without assignments
with COUNT_CTE as
(
select
VOLUNTEERASSIGNMENT.JOBOCCURRENCEID as ID,
count(1) as ASSIGNMENTCOUNT
from dbo.VOLUNTEERASSIGNMENT
inner join @ITINERARYITEMSTAFFRESOURCES as STAFFRESOURCES on
STAFFRESOURCES.JOBOCCURRENCEID = VOLUNTEERASSIGNMENT.JOBOCCURRENCEID
group by VOLUNTEERASSIGNMENT.JOBOCCURRENCEID
)
insert into @LIST
select
STAFFRESOURCES.ID as ID,
STAFFRESOURCES.ITINERARYNAME as ITINERARYNAME,
STAFFRESOURCES.ITINERARYITEMNAME as ITINERARYITEMNAME,
STAFFRESOURCES.STARTTIME as STARTTIME,
STAFFRESOURCES.ENDTIME as ENDTIME,
STAFFRESOURCES.VOLUNTEERTYPE as VOLUNTEERTYPE,
'(Unassigned)' as ASSIGNEDTO,
STAFFRESOURCES.QUANTITYNEEDED - coalesce(COUNT.ASSIGNMENTCOUNT,0) as QUANTITYOPEN,
0 as ISSCHEDULED,
STAFFRESOURCES.FILLEDBYCODE,
0 as ISITINERARYLEVEL,
null as CONSTITUENTID
from @ITINERARYITEMSTAFFRESOURCES as STAFFRESOURCES
left join COUNT_CTE as COUNT on
STAFFRESOURCES.JOBOCCURRENCEID = COUNT.ID
where
STAFFRESOURCES.FILLEDBYCODE = 0 and
(
COUNT.ID is null or
STAFFRESOURCES.QUANTITYNEEDED <> COUNT.ASSIGNMENTCOUNT
)
-- Select all volunteer type itinerary staff resources with assignments
insert into @LIST
select
STAFFRESOURCES.ID as ID,
STAFFRESOURCES.ITINERARYNAME as ITINERARYNAME,
'' as ITINERARYITEMNAME,
STAFFRESOURCES.STARTTIME as STARTTIME,
STAFFRESOURCES.ENDTIME as ENDTIME,
STAFFRESOURCES.VOLUNTEERTYPE as VOLUNTEERTYPE,
dbo.UFN_CONSTITUENT_BUILDNAME(VOLUNTEERASSIGNMENT.VOLUNTEERID) as ASSIGNEDTO,
null as QUANTITYOPEN,
1 as ISSCHEDULED,
STAFFRESOURCES.FILLEDBYCODE,
1 as ISITINERARYLEVEL,
VOLUNTEERASSIGNMENT.VOLUNTEERID as CONSTITUENTID
from dbo.VOLUNTEERASSIGNMENT
inner join @ITINERARYSTAFFRESOURCES as STAFFRESOURCES on
VOLUNTEERASSIGNMENT.JOBOCCURRENCEID = STAFFRESOURCES.JOBOCCURRENCEID
where
STAFFRESOURCES.FILLEDBYCODE = 0
-- Select all volunteer type itinerary item staff resources with assignments
insert into @LIST
select
STAFFRESOURCES.ID as ID,
STAFFRESOURCES.ITINERARYNAME as ITINERARYNAME,
STAFFRESOURCES.ITINERARYITEMNAME as ITINERARYITEMNAME,
STAFFRESOURCES.STARTTIME as STARTTIME,
STAFFRESOURCES.ENDTIME as ENDTIME,
STAFFRESOURCES.VOLUNTEERTYPE as VOLUNTEERTYPE,
dbo.UFN_CONSTITUENT_BUILDNAME(VOLUNTEERASSIGNMENT.VOLUNTEERID) as ASSIGNEDTO,
null as QUANTITYOPEN,
1 as ISSCHEDULED,
STAFFRESOURCES.FILLEDBYCODE,
0 as ISITINERARYLEVEL,
VOLUNTEERASSIGNMENT.VOLUNTEERID as CONSTITUENTID
from dbo.VOLUNTEERASSIGNMENT
inner join @ITINERARYITEMSTAFFRESOURCES as STAFFRESOURCES on
VOLUNTEERASSIGNMENT.JOBOCCURRENCEID = STAFFRESOURCES.JOBOCCURRENCEID
where
STAFFRESOURCES.FILLEDBYCODE = 0
select
ID,
VOLUNTEERTYPE,
case
when coalesce(QUANTITYOPEN,0) < 0 then '(Overassigned)'
else ASSIGNEDTO
end as ASSIGNEDTO,
STARTTIME,
ENDTIME,
ITINERARYNAME,
ITINERARYITEMNAME,
QUANTITYOPEN,
case
when ISSCHEDULED = 1 then 'Assigned'
else
case
when coalesce(QUANTITYOPEN,0) < 0 then 'Overassigned'
else 'Unassigned'
end
end as ISASSIGNED,
FILLEDBYCODE,
ISITINERARYLEVEL,
CONSTITUENTID
from @LIST