UFN_GROUPSALESDEFAULT_GETCAPACITYFORDATESEXCLUDINGRESERVATION

Returns a table with capacity remaining for dates in group sales excluding the capacity used from the reservation passed in.

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode
@STARTDATE	date	IN
@ENDDATE	date	IN
@IGNORERESERVATIONID	uniqueidentifier	IN

Definition

Copy



CREATE function dbo.UFN_GROUPSALESDEFAULT_GETCAPACITYFORDATESEXCLUDINGRESERVATION
(
    @STARTDATE date = null,
    @ENDDATE date = null,
    @IGNORERESERVATIONID uniqueidentifier = null
)
returns @DATESWITHCAPACITY table(COMPAREDATE date, CAPACITY int) 
as
begin
    with ITINERARIES_CTE as (
        select
            isnull(min(ITINERARYDATES.STARTDATE), RESERVATION.ARRIVALDATE) as STARTDATE,
            isnull(max(ITINERARYDATES.ENDDATE), RESERVATION.ARRIVALDATE) as ENDDATE,
            ITINERARYCAPACITIES.QUANTITY
        from
            dbo.ITINERARY
        inner join
            dbo.RESERVATION on RESERVATION.ID = ITINERARY.RESERVATIONID
        inner join
            dbo.SALESORDER on SALESORDER.ID = ITINERARY.RESERVATIONID
        outer apply (
            select min(STARTDATE) as STARTDATE, max(ENDDATE) as ENDDATE
            from dbo.ITINERARYITEM
            where ITINERARYID = ITINERARY.ID
        ) ITINERARYDATES
        cross apply (
            select sum(ITINERARYATTENDEE.QUANTITY) as QUANTITY
            from dbo.ITINERARYATTENDEE
            where ITINERARYATTENDEE.ITINERARYID = ITINERARY.ID
        ) ITINERARYCAPACITIES
        where
            SALESORDER.STATUSCODE <> 5  -- Cancelled

            and (@IGNORERESERVATIONID is null or ITINERARY.RESERVATIONID <> @IGNORERESERVATIONID)
            and (
                RESERVATION.ARRIVALDATE <= @ENDDATE
                -- This check seems strange, but it helps take advantage of the

                -- ARRIVALDATE index.  The following check will give us the actual results.

                and RESERVATION.ARRIVALDATE >= dateadd(day, -5, @STARTDATE)
                and isnull(ITINERARYDATES.ENDDATE, RESERVATION.ARRIVALDATE) >= @STARTDATE
            )
        group by
            ITINERARY.ID, ITINERARYDATES.STARTDATE, ITINERARYDATES.ENDDATE, RESERVATION.ARRIVALDATE, ITINERARYCAPACITIES.QUANTITY
    )
    insert into @DATESWITHCAPACITY
        select
            D.DATE,
            sum(ITINERARIES_CTE.QUANTITY) as CAPACITY
        from
            dbo.UFN_CALENDARDATES(@STARTDATE, @ENDDATE, 1) as D
        inner join
            ITINERARIES_CTE on D.DATE between ITINERARIES_CTE.STARTDATE and ITINERARIES_CTE.ENDDATE
        group by
            D.DATE
        option (recompile);

    return;

end