Creation of SQL query

Question

I have a query issue .. Basically in my hotel booking website there is Reservations the reservation has a Check in date and a Check out date

.

I need an SQL query which

detects if a new check in and checkout date is in the range of any check in and check out date in the table.

My current query doesn't work because it doesn't detect when a newly entered check in date starts before the database checkin date and a newly entered checkout date finishes after the checkout date in the database.

Thanks for your help!

Current Code ( I wasnt sure if you wanted it in pure sql or ...

// Gets the Checkin and checkout dates from the session variable
        $FirstDate = $request->session()->get('checkin');
        $SecDate = $request->session()->get('checkout');

        // Tries to match the checkin and checkout dates with other reservations of the same Hotel room
        foreach ($Rooms as $Room) {
            $Id =  $Room->id;

            $RoomsBooked = Reservation::where('room_id','=', $Id)
            ->where('CheckIn','>=',$FirstDate)
            ->where('CheckOut','<=',$SecDate)

                ->orWhere(function($query) use ($FirstDate,$Id)
                    {
                      $query->where('room_id','=', $Id)
                            ->where('CheckIn','<=',$FirstDate)

                            ->where('CheckOut','>=',$FirstDate);
                          })

                            ->orWhere(function($query2) use ($FirstDate,$SecDate,$Id)
                            {
                              $query2->where('room_id','=', $Id)
                                    ->where('CheckIn','>=',$FirstDate)

                                    ->where('CheckIn','<=',$SecDate);
                                  })->count();


                  // Works out How many rooms are available
                  $Roomsavailable = $Room->TotalRooms;
                  $Roomsleft =  $Roomsavailable - $RoomsBooked;
                  $Room->spaceleft = $Roomsleft;
            }

Answer 1

The basic logic to check whether two date ranges overlap is simply:

where start1 < end2 and end1 > start2

Replace < with <= etc. if you want the same date to count as overlap.

So, within your code, I think you just need a single place that checks:

        ->where('CheckIn','<=',$SecondDate)
        ->where('CheckOut','>=',$FirstDate);