UFN_GROUPSALESCAPACITY_CAPACITYREMAINING

        CREATE function dbo.UFN_GROUPSALESCAPACITY_CAPACITYREMAINING
        (
            @DATE date, 
            @IGNOREITINERARY uniqueidentifier = null
        )
        returns int
        as 
        begin
            declare @CAPACITYREMAINING int;

            with ITINERARY_CTE as (
                select 
                    ITINERARY.ID
                from dbo.ITINERARY
                inner join dbo.RESERVATION on 
                    RESERVATION.ID = ITINERARY.RESERVATIONID
                inner join dbo.SALESORDER on 
                    SALESORDER.ID = RESERVATION.ID
                where 
                    (SALESORDER.STATUSCODE <> 5)
                    and (@IGNOREITINERARY is null or @IGNOREITINERARY <> ITINERARY.ID)
                    -- Since reservations can only be for 5 days, this allows sql server to narrow

                    -- the reservations it looks at instead of scanning entire tables.

                    and RESERVATION.ARRIVALDATE between dateadd(day, -5, @DATE) and dateadd(day, 5, @DATE)
                    and (
                        @DATE between
                            isnull((select min(STARTDATE) from dbo.ITINERARYITEM where ITINERARYID = ITINERARY.ID), RESERVATION.ARRIVALDATE) and
                            isnull((select max(ENDDATE) from dbo.ITINERARYITEM where ITINERARYID = ITINERARY.ID), RESERVATION.ARRIVALDATE)
                    )
            )

            select 
                @CAPACITYREMAINING = isnull(sum(ITINERARYATTENDEE.QUANTITY), 0)
            from ITINERARY_CTE
            inner join dbo.ITINERARYATTENDEE on 
                ITINERARYATTENDEE.ITINERARYID = ITINERARY_CTE.ID

            declare @MAXIMUMCAPACITY int;
            select top 1 @MAXIMUMCAPACITY = isnull(MAXIMUMCAPACITY, 0)
            from dbo.GROUPSALESDEFAULT

            set @CAPACITYREMAINING = @MAXIMUMCAPACITY - @CAPACITYREMAINING;

            if @CAPACITYREMAINING < 0
                set @CAPACITYREMAINING = 0;

            return @CAPACITYREMAINING;
      end