Find elements between two dates and group them

Question

I have this table:

Table ___Bookings:

|--------|------------|--------------|------------|------------|
| BOO_Id | BOO_RoomId | BOO_ClientId | BOO_DateCI | BOO_DateCO |
|--------|------------|--------------|------------|------------|
| 1      | 9          | 45           | 2018-01-02 | 2018-01-03 |
| 2      | 4          | 46           | 2017-12-30 | 2018-01-07 |
| 3      | 3          | 2            | 2018-12-31 | 2018-01-01 |
| 4      | 9          | 98           | 2018-01-05 | 2018-01-10 |
|--------|------------|--------------|------------|------------|

I'm looking to display, per day, the bookings in departure, in arrival or in house.

The desired output should be something like this:

2017-12-30  =  Booking #2 in arrival.
2017-12-31  =  Booking #3 in arrival.
            =  Booking #1 in arrival.
            =  Booking #2 in house.
2018-01-01  =  Booking #3 in departure.
            =  Booking #2 in house.
2018-01-02  =  Booking #1 in arrival.
            =  Booking #2 in house.
2018-01-03  =  Booking #1 in departure.
            =  Booking #2 in house.
2018-01-04  =  Booking #2 in house.
2018-01-05  =  Booking #4 in arrival.
            =  Booking #2 in house.
2018-01-06  =  Booking #2 in house.
            =  Booking #4 in house.
2018-01-07  =  Booking #2 in departure.
            =  Booking #4 in house.
2018-01-08  =  Booking #4 in house.
2018-01-09  =  Booking #4 in house.
2018-01-10  =  Booking #4 in departure.

What I already tried:

SELECT *,
    CASE 
      WHEN BOO_DateCI = '2017-04-01' THEN 'In Arrival'
      WHEN BOO_DateCO = '2017-04-30' THEN 'In Departure'
      WHEN '2017-04-01' > BOO_DateCI AND '2017-04-01' < BOO_DateCO THEN 'In House'
    END
FROM ___Bookings
WHERE BOO_DateCI = '2017-04-01'
OR BOO_DateCO = '2017-04-30'
OR '2017-04-01' BETWEEN BOO_DateCI AND BOO_DateCO;

But I can't get the in house bookings and can't group the booking depending the status arrival, departure or in-house.

Answer 1

The thing you are missing is a list of days, if you don't have a dedicated date dimension in the DB, you can do it manually like this:

DECLARE @start DATE, @end DATE;
SELECT @start = '20171215', @end = '20180107';

;WITH n AS 
(
  SELECT TOP (DATEDIFF(DAY, @start, @end) + 1) 
   n = ROW_NUMBER() OVER (ORDER BY [object_id])
  FROM sys.all_objects
)
SELECT DATEADD(DAY, n-1, @start) as Date
into #Days
FROM n;

Now that we have a list of days, it's just a matter of joining your list to the list of days.

select d.date
    , case when b.boo_id is null then 'No Bookings' else 'Booking #' + 
        cast(b.boo_id as varchar(255)) +
        ' In ' + case when b.boo_id is null then ''
            when d.date = b.BOO_DateCI then 'Checking In'
            when d.date = b.BOO_DateCO Then 'Checking Out'
            else 'In House' end as BookingStatus
from #Days d
  left outer join ___Bookings b
    on d.date between b.BOO_DateCI and b.BOO_DateCO

We use a left outer join in case there is a day without guest, and since we use left we have to account for nulls gracefully, which is the job of the isnull and the case statement. The rest is some deductive reasoning, date_CI is probably check in and date_CO is probably Check out. Hope that helps!