UFN_GROUPSALESCAPACITY_CAPACITYREMAININGWITHOUTRESERVATION

        CREATE function dbo.UFN_GROUPSALESCAPACITY_CAPACITYREMAININGWITHOUTRESERVATION
        (
            @DATE date, 
            @IGNORERESERVATION 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
                    (@IGNORERESERVATION is null or @IGNORERESERVATION <> RESERVATION.ID) 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