USP_DATALIST_GROUPSALESCHECKIN

            CREATE procedure dbo.USP_DATALIST_GROUPSALESCHECKIN
            (
                @DATESELECTTYPE tinyint = null,
                @STARTDATE date = null,
                @ENDDATE date = null,
                @INCLUDECHECKEDIN bit = 0
            )
            as
                set nocount on;

                declare @CURRENTDATE date;
                set @CURRENTDATE = getdate();

                if @DATESELECTTYPE is null
                    set @DATESELECTTYPE = 0;

                if @INCLUDECHECKEDIN is null
                    set @INCLUDECHECKEDIN = 0;

                set @STARTDATE = case @DATESELECTTYPE
                    when 0 then @CURRENTDATE 
                    when 1 then @CURRENTDATE 
                    when 3 then @STARTDATE
                    when 4 then @STARTDATE
                end;

                set @ENDDATE = case @DATESELECTTYPE
                    when 0 then @CURRENTDATE 
                    when 1 then dateadd(week,1, @CURRENTDATE) 
                    when 3 then @ENDDATE
                    when 4 then @STARTDATE
                end;

                declare @ITINERARYCOUNT tinyint;

                select
                    RESERVATION.ID,
                    case when ARRIVALDATE <= @CURRENTDATE and STATUSCODE not in (1, 5) then 1
                        else 0 end CANCOMPLETE,
                    NAME,
                    case when STATUSCODE = 1 then 'RES:check'
                        else 'RES:bullet_crystal'
                    end CHECKEDIN,
                    case when ARRIVALTIME = '' then 
                        (case when ((select count(ITINERARYITEM.ID) 
                                    from dbo.ITINERARY 
                                    inner join dbo.ITINERARYITEM on ITINERARYITEM.ITINERARYID = ITINERARY.ID
                                    where RESERVATIONID = RESERVATION.ID) = 0)
                            then ''
                        else
                            dbo.UFN_HOURMINUTE_GETFROMDATE(STARTDATETIME) end) 
                    else ARRIVALTIME end ARRIVALTIME,
                    dbo.UFN_RESERVATION_TOTALVISITORCOUNT(RESERVATION.ID) as NUMBEROFVISITORS,
                    ARRIVALDATE,
                    STATUSCODE,
                    coalesce((select count(ITINERARY.ID)
                                                from dbo.RESERVATION RES
                                                inner join dbo.ITINERARY on RES.ID = ITINERARY.RESERVATIONID
                                                where RES.ID = RESERVATION.ID), 0) as ITINERARYCOUNT
                    from dbo.RESERVATION
                inner join dbo.SALESORDER on RESERVATION.ID = SALESORDER.ID
                where ((ARRIVALDATE >= @STARTDATE and ARRIVALDATE <= @ENDDATE) or (@DATESELECTTYPE = 2))
                    and (((@INCLUDECHECKEDIN = 0) and (STATUSCODE not in (1, 5))) or ((@INCLUDECHECKEDIN = 1) and (STATUSCODE <> 5)))
                order by ARRIVALTIME asc;

            return 0;