Select d, Sum(Case When d=Arrival Then 1 Else 0 End) ArrivalCount,
         Sum(Case When d=Arrival Then Adult Else 0 End) ArrivalAdultCount,
         Sum(Case When d=Arrival Then Children Else 0 End) ArrivalChildrenCount,
         Sum(Case When d=Arrival Then ChildrenNC Else 0 End) ArrivalChildrenNCCount,
       Sum(Case When ((d=Arrival) and (gen.IsPreregistered = True)) Then 1 Else 0 End) PreregisteredCount,
         
        Sum(Case When d>=Arrival and d < Departure Then 1 Else 0 End) InHouseCount,
        Sum(Case When d>=Arrival and d < Departure Then Adult Else 0 End) InHouseAdultCount,
        Sum(Case When d>=Arrival and d < Departure Then Children Else 0 End) InhouseChildrenCount,
        Sum(Case When d>=Arrival and d < Departure Then ChildrenNC Else 0 End) InHouseChildrenNCCount,

        Sum(Case When d = Departure Then 1 Else 0 End) DepartureCount,
        Sum(Case When d=Departure Then Adult Else 0 End) DepartureAdultCount,
        Sum(Case When d=Departure Then Children Else 0 End) DepartureChildrenCount,
        Sum(Case When d=Departure Then ChildrenNC Else 0 End) DepartureChildrenNCCount,
        
        (Select Count(*) from cfgroom Where RoomTypeId in (Select ID from cfgRoomType)) TotalRoomCount
    from Dates
       inner join tblGuestDates dt on d >= Arrival and d <= Departure
       inner join tblGuestGeneral gen on dt.ResNumber = gen.ResNumber
    Where D >= DATE '2017-6-30' and D < DATE '2017-6-30' + Interval '7' Day
      and Cancelled Is Null
      and Exists(Select ResNumber from tblGuestRoomRates rr Where rr.ResNumber = dt.ResNumber and RoomTypeId in (Select ID from cfgRoomType))
    Group By D

---

 Comments and Workarounds

---

The new JOININDEXTHRESHHOLD clause in 2.26 triggered an existing bug in the evaluation of WHERE conditions after joins had been executed. This evaluation was only evaluating the first after-join WHERE condition, even if there were multiple such conditions present.

A workaround for fixing this issue is to use this clause at the end of the query:

JOININDEXTHRESHHOLD 120

which will turn off the new join index threshhold optimization in 2.26 B1.

---

 Resolution

---

Fixed Problem on 7/2/2017 in version 2.26 build 2

---

 Products Affected

---

ElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB LCL Standard with Source
ElevateDB PHP Standard
ElevateDB PHP Standard with Source
ElevateDB PHP Trial
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial