USP_ITINERARY_CHECKPERTICKETRESOURCES

        CREATE procedure dbo.USP_ITINERARY_CHECKPERTICKETRESOURCES
        (
            @ID uniqueidentifier
        )
        as
        begin

            declare @VISITORCOUNT int
            set @VISITORCOUNT = isnull((
                                    select sum(ITINERARYATTENDEE.QUANTITY)
                                    from dbo.ITINERARYATTENDEE
                                    where ITINERARYID = @ID
                                ),0);

            declare @RESOURCEID uniqueidentifier
            declare @NAME nvarchar(100)
            declare @TOTALQUANTITY as int
            declare @QUANTITYNEEDED as int

            select top 1
                @RESOURCEID = ID,
                @NAME = NAME,
                @TOTALQUANTITY = TOTALQUANTITY,
                @QUANTITYNEEDED = QUANTITYNEEDED
            from
            (
                select
                    RESOURCE.ID as ID,
                    RESOURCE.NAME as NAME,
                    RESOURCE.QUANTITY as TOTALQUANTITY,
                    dbo.UFN_RESOURCE_CALCULATEPERTICKETQUANTITY_2
                        (@VISITORCOUNT * RESOURCE.PERTICKETQUANTITY, RESOURCE.PERTICKETDIVISOR) as QUANTITYNEEDED
                from dbo.ITINERARYRESOURCE
                inner join dbo.RESOURCE on ITINERARYRESOURCE.RESOURCEID = RESOURCE.ID
                inner join dbo.ITINERARY on ITINERARYRESOURCE.ITINERARYID = ITINERARY.ID
                where
                    RESOURCE.TYPECODE = 0 and
                    ITINERARY.ID = @ID and
                    ITINERARYRESOURCE.IGNORESQUANTITYFORCAPACITY = 0 and
                    RESOURCE.ISPERTICKETITEM = 1

                union

                select
                    RESOURCE.ID as ID,
                    RESOURCE.NAME as NAME,
                    RESOURCE.QUANTITY as TOTALQUANTITY,
                    dbo.UFN_RESOURCE_CALCULATEPERTICKETQUANTITY_2
                        (@VISITORCOUNT * RESOURCE.PERTICKETQUANTITY, RESOURCE.PERTICKETDIVISOR) as QUANTITYNEEDED
                from dbo.ITINERARYITEMRESOURCE
                inner join dbo.RESOURCE on ITINERARYITEMRESOURCE.RESOURCEID = RESOURCE.ID
                inner join dbo.ITINERARYITEM on ITINERARYITEMRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
                where
                    RESOURCE.TYPECODE = 0 and
                    ITINERARYITEM.ITINERARYID = @ID and
                    ITINERARYITEMRESOURCE.IGNORESQUANTITYFORCAPACITY = 0 and
                    ITINERARYITEM.ITEMTYPECODE <> 3 and
                    RESOURCE.ISPERTICKETITEM = 1
            ) as T
            where
                QUANTITYNEEDED > TOTALQUANTITY

            if @RESOURCEID is not null
            begin
                declare @ERRORMESSAGE nvarchar(500)    
                set @ERRORMESSAGE = convert(nvarchar(10), @QUANTITYNEEDED) + ' ' + @NAME + 
                    '(s) are required for this group.  Only ' + convert(nvarchar(10), @TOTALQUANTITY) + ' are available.  ' +
                    'You can either reduce the group size or remove the resource.'

                raiserror(@ERRORMESSAGE, 13, 1)            
            end


        end