USP_DATALIST_RESERVATIONSTATUSCOMPLETE

                CREATE procedure dbo.USP_DATALIST_RESERVATIONSTATUSCOMPLETE
                (
                    @STARTDATE date = null,
                    @ENDDATE date = null
                ) 
                as
                    set nocount on;

                    with RESERVATIONS_CTE as
                    (
                        select RESERVATION.ID,
                            NAME,
                            case when ARRIVALTIME = '' then (isnull((select min(ITINERARYITEM.STARTTIME)
                                    from dbo.ITINERARYITEM
                                    inner join dbo.ITINERARY on ITINERARY.ID = ITINERARYITEM.ITINERARYID
                                    where ITINERARY.RESERVATIONID = RESERVATION.ID and
                                        ITINERARYITEM.STARTDATE = RESERVATION.ARRIVALDATE
                                    ), '0800')) else ARRIVALTIME end STARTTIME,
                            isnull(LATESTITINERARYENDDATESANDTIMES.ENDTIME, case when RESERVATION.ARRIVALTIME = '' then '0900' else RESERVATION.ARRIVALTIME end) as ENDTIME,
                            ARRIVALDATE as STARTDATE,
                            isnull(LATESTITINERARYENDDATESANDTIMES.ENDDATE, RESERVATION.ARRIVALDATE) as ENDDATE,
                            SALESORDER.STATUSCODE STATUSCODE,
                            SALESORDER.STATUS STATUS,
                            dbo.UFN_RESERVATION_GETPAYMENTCOUNT(RESERVATION.ID) as PAYMENTCOUNT,
                            isnull(dbo.UFN_RESERVATION_BUILDITINERARYITEMSSTRING(RESERVATION.ID) , '') as DESCRIPTION
                        from dbo.RESERVATION
                        inner join dbo.SALESORDER on SALESORDER.ID = RESERVATION.ID
                        outer apply (
                            select top 1 ITINERARYITEM.ENDDATE, ITINERARYITEM.ENDTIME
                            from dbo.ITINERARY
                            inner join dbo.ITINERARYITEM on ITINERARYITEM.ITINERARYID = ITINERARY.ID
                            where ITINERARY.RESERVATIONID = RESERVATION.ID
                            order by ITINERARYITEM.ENDDATETIME desc
                        ) LATESTITINERARYENDDATESANDTIMES
                        where
                            SALESORDER.STATUSCODE = 1
                            and (@STARTDATE is null or RESERVATION.ARRIVALDATE >= @STARTDATE)
                            and (@ENDDATE is null or RESERVATION.ARRIVALDATE <= @ENDDATE)
                    )
                    select
                        ID,
                        NAME,
                        STARTTIME,
                        ENDTIME,
                        STARTDATE,
                        ENDDATE,
                        STATUSCODE,
                        PAYMENTCOUNT,
                        convert(nvarchar(10), dbo.UFN_RESERVATION_TOTALVISITORCOUNT(ID)) + ' Visitors' + 
                            char(10) + 'Status: ' + STATUS + char(10) + DESCRIPTION
                    from RESERVATIONS_CTE
                    option (recompile);

                    return 0;