UFN_RESERVATION_HASUNASSIGNEDSTAFFRESOURCES

        create function dbo.UFN_RESERVATION_HASUNASSIGNEDSTAFFRESOURCES
        (
            @ID uniqueidentifier
        )
        returns bit
        with execute as caller
        as 
        begin

            declare @HASUNASSIGNEDSTAFFRESOURCES bit

            set @HASUNASSIGNEDSTAFFRESOURCES = 0

            declare @ITINERARYSTAFFRESOURCES table
            (
                ID uniqueidentifier,
                QUANTITYNEEDED int,
                JOBOCCURRENCEID uniqueidentifier,
                FILLEDBYCODE tinyint
            )
            insert into @ITINERARYSTAFFRESOURCES
            select
                ITINERARYSTAFFRESOURCE.ID as ID,
                ITINERARYSTAFFRESOURCE.QUANTITYNEEDED as QUANTITYNEEDED,
                ITINERARYSTAFFRESOURCE.JOBOCCURRENCEID as JOBOCCURENCEID,
                ITINERARYSTAFFRESOURCE.FILLEDBYCODE
            from dbo.ITINERARYSTAFFRESOURCE
            inner join dbo.ITINERARY on
                ITINERARYSTAFFRESOURCE.ITINERARYID = ITINERARY.ID
            where
                ITINERARY.RESERVATIONID = @ID and
                ITINERARY.STARTDATETIME is not null and
                ITINERARY.ENDDATETIME is not null

            declare @ITINERARYITEMSTAFFRESOURCES table
            (
                ID uniqueidentifier,
                QUANTITYNEEDED int,
                JOBOCCURRENCEID uniqueidentifier,
                FILLEDBYCODE tinyint
            )
            insert into @ITINERARYITEMSTAFFRESOURCES
            select
                ITINERARYITEMSTAFFRESOURCE.ID as ID,
                ITINERARYITEMSTAFFRESOURCE.QUANTITYNEEDED as QUANTITYNEEDED,
                ITINERARYITEMSTAFFRESOURCE.JOBOCCURRENCEID as JOBOCCURENCEID,
                ITINERARYITEMSTAFFRESOURCE.FILLEDBYCODE
            from dbo.ITINERARYITEMSTAFFRESOURCE
            inner join dbo.ITINERARYITEM on
                ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
            inner join dbo.ITINERARY on
                ITINERARYITEM.ITINERARYID = ITINERARY.ID
            where
                ITINERARY.RESERVATIONID = @ID and 
                ITINERARYITEM.ITEMTYPECODE <> 3;

            -- 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
            )
            select @HASUNASSIGNEDSTAFFRESOURCES = 1
            from @ITINERARYSTAFFRESOURCES as STAFFRESOURCES
            left join COUNT_CTE as COUNT on STAFFRESOURCES.ID = COUNT.ID
            where
                STAFFRESOURCES.FILLEDBYCODE <> 0 and
                (
                    (COUNT.ID is null and STAFFRESOURCES.QUANTITYNEEDED > 0) 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
            )
            select @HASUNASSIGNEDSTAFFRESOURCES = 1
            from @ITINERARYITEMSTAFFRESOURCES as STAFFRESOURCES
            left join COUNT_CTE as COUNT on STAFFRESOURCES.ID = COUNT.ID
            where
                STAFFRESOURCES.FILLEDBYCODE <> 0 and
                (
                    (COUNT.ID is null and STAFFRESOURCES.QUANTITYNEEDED > 0) or
                    STAFFRESOURCES.QUANTITYNEEDED <> COUNT.ASSIGNMENTCOUNT
                );

            -- 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
            )
            select @HASUNASSIGNEDSTAFFRESOURCES = 1
            from @ITINERARYSTAFFRESOURCES as STAFFRESOURCES
            left join COUNT_CTE as COUNT on STAFFRESOURCES.JOBOCCURRENCEID = COUNT.ID
            where
                STAFFRESOURCES.FILLEDBYCODE = 0 and
                (
                    (COUNT.ID is null and STAFFRESOURCES.QUANTITYNEEDED > 0) 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
            )
            select @HASUNASSIGNEDSTAFFRESOURCES = 1
            from @ITINERARYITEMSTAFFRESOURCES as STAFFRESOURCES
            left join COUNT_CTE as COUNT on STAFFRESOURCES.JOBOCCURRENCEID = COUNT.ID
            where
                STAFFRESOURCES.FILLEDBYCODE = 0 and
                (
                    (COUNT.ID is null and STAFFRESOURCES.QUANTITYNEEDED > 0) or
                    STAFFRESOURCES.QUANTITYNEEDED <> COUNT.ASSIGNMENTCOUNT
                )

            return @HASUNASSIGNEDSTAFFRESOURCES
        end