USP_DATALIST_RESERVATIONSFORDAY

                CREATE procedure dbo.USP_DATALIST_RESERVATIONSFORDAY(@GIVENDATE date)
                as
                    set nocount on;

                    declare @UPPERBOUND datetime;
                    set @UPPERBOUND = dbo.UFN_DATE_GETLATESTTIME(@GIVENDATE);

                    select
                        RESERVATION.ID,
                        RESERVATION.NAME,
                        case
                            when RESERVATION.ARRIVALDATE = @GIVENDATE then
                                case
                                    when len(RESERVATION.ARRIVALTIME) > 0 then
                                        RESERVATION.ARRIVALTIME
                                    else
                                        dbo.UFN_HOURMINUTE_GETFROMDATE(min(ITINERARY.STARTDATETIME))
                                end
                            else
                                case
                                    when min(ITINERARY.STARTDATETIME) < @GIVENDATE then
                                        '0000'
                                    else
                                        dbo.UFN_HOURMINUTE_GETFROMDATE(min(ITINERARY.STARTDATETIME))
                                end
                        end as [STARTTIME],
                        case
                            when max(ITINERARY.ENDDATETIME) > @UPPERBOUND then
                                '2359'
                            else
                                dbo.UFN_HOURMINUTE_GETFROMDATE(max(ITINERARY.ENDDATETIME))
                        end as [ENDTIME],
                        sum(dbo.UFN_ITINERARY_TOTALVISITORCOUNT(ITINERARY.ID)) as [NUMBEROFVISITORS]
                    from
                        dbo.RESERVATION
                    inner join 
                        dbo.SALESORDER
                            on SALESORDER.ID = RESERVATION.ID
                    left outer join
                        dbo.ITINERARY
                            on RESERVATION.ID = ITINERARY.RESERVATIONID
                            and (
                                ITINERARY.STARTDATETIME is null
                                or ITINERARY.STARTDATETIME between @GIVENDATE and @UPPERBOUND
                                or ITINERARY.ENDDATETIME between @GIVENDATE and @UPPERBOUND
                                or (ITINERARY.STARTDATETIME < @GIVENDATE and ITINERARY.ENDDATETIME > @UPPERBOUND)
                            )
                    where
                        (SALESORDER.STATUSCODE <> 5 )
                        and (RESERVATION.ARRIVALDATE = @GIVENDATE
                        or ITINERARY.STARTDATETIME is not null)
                    group by
                        RESERVATION.ID,
                        RESERVATION.NAME,
                        RESERVATION.ARRIVALDATE,
                        RESERVATION.ARRIVALTIME;

                    return 0;