T-SQL adding dynamic rows into resultset

Question

I have two tables:

• Room
• Contract (Rent Contracts)
• Relationship: Contract n-1 Room

I get user input startDate & endDate. On base of that interval I query what Contracts where relevant for the Rooms. Relevant means:

• A Room is being rented throughout the given interval
• A Room begins being rented in the given interval
• A Room stops being rented in the given interval

My query is:

SELECT Room.id,
       RentContract.activeon,
       RentContract.expireson
FROM RentContract
INNER JOIN Room ON RentContract.roomid = Room.id
WHERE (RentContract.new_activeon >= @startDate
       OR RentContract.new_activeon IS NULL)
  AND (RentContract.new_expireson <= @endDate
       OR RentContract.new_expireson IS NULL)

Now, the requirement is that I additionally show the non-rented intervals for these rooms. As I don't have that in my db, I guess I need to insert some kind of dynamic rows, which I will show on that same list. Additionally I will show an additional column in the result (State), which will show "Occupied" for the actual contracts and "empty" for the "dynamic" rows.

So, as an example, user input is: startDate = 01.05.2016, endDate = 01.07.2016

My result right now is:

My desired result is:

So I actually need to "fill" the whole input interval either with db records or dynamic records

---

@Rhumborl Your solution almost worked out for me! There is one little detail left:

I get some results, where a contract begins on the 2nd of a month, so I guess I would need to have an "Empty" entry for that day. E.g. 01.01.2016 to 01.01.2016 Empty. Here is some subset of my initial result and the result I got from you solution (I mark as yellow the specific scenario):

Initial query:

Rhumborl's query:

Is there a little tweak on your query to solve that?

Answer 1

You need a couple of CTEs to work this out. The basic idea is to get all the occupied times as you already have, then use the dates in that result set to find the gaps for each room.

First here is the full query:

declare @startDate smalldatetime = '20160501',
        @endDate smalldatetime = '20160701'

; with occupieds as (
    SELECT Room.id,
           RentContract.activeon,
           RentContract.expireson,
           'Occupied' as [State],
           -- get ordering of contract for each rooms
           row_number() over (partition by roomid order by activeon) SortOrder
    FROM RentContract
    INNER JOIN Room ON RentContract.roomid = Room.id
    WHERE (RentContract.activeon >= @startDate
           OR RentContract.activeon IS NULL)
      AND (RentContract.expireson <= @endDate
           OR RentContract.expireson IS NULL)
),
empties as (
    select o1.id, o1.expireson + 1 as activeon, o2.activeon - 1 as expireson, 'Empty' as [State] from occupieds o1
        inner join occupieds o2 on o1.id = o2.id and o1.SortOrder = o2.SortOrder - 1
),
extremes as (
    select id, @startDate as activeon, min(activeon) - 1 as expireson, 'Empty' as [State] from occupieds group by id
        having min(activeon) > @startDate
    union all
    select id, max(expireson) + 1 as activeon, @endDate as expireson, 'Empty' as [State] from occupieds group by id
        having max(expireson) < @enddate
)
select id, activeon, expireson, [State] from occupieds
    union all
select id, activeon, expireson, [State] from empties
    union all
select id, activeon, expireson, [State] from extremes
order by id, activeon

Let's break it down

Step 1 - Occupied Rooms

This is almost identical to your current query. The only addition is that we will use row_number() to get an ordering of the contracts for each room. We will use that in the next step.

SELECT Room.id,
       RentContract.activeon,
       RentContract.expireson,
       'Occupied' as [State],
       -- get ordering of contract for each rooms
       row_number() over (partition by roomid order by activeon) SortOrder
FROM RentContract
INNER JOIN Room ON RentContract.roomid = Room.id
WHERE (RentContract.activeon >= @startDate
       OR RentContract.activeon IS NULL)
  AND (RentContract.expireson <= @endDate
       OR RentContract.expireson IS NULL)

This gives the following

id  | activeon            | expireson           | State    | SortOrder
1   | 2016-05-01 00:00:00 | 2016-05-31 00:00:00 | Occupied | 1
1   | 2016-06-15 00:00:00 | 2016-06-25 00:00:00 | Occupied | 2
2   | 2016-05-01 00:00:00 | 2016-07-01 00:00:00 | Occupied | 1

Step 2 - empty rooms between contracts

Now we have the contracts for each room and the order they appear, we can use a self-join between one contract and the next to work out the date range it is empty. So select the row, then join to itself on the same roomid with the previous SortOrder. In the table above, row 1 will get joined to row 2. This gives us a start date (the expireson for row 1) and an end date (the active on for row 2). The we just add/minus a day so they don't overlap:

select o1.id, o1.expireson + 1 as activeon, o2.activeon - 1 as expireson, 'Empty' as [State] from occupieds o1
    inner join occupieds o2 on o1.id = o2.id and o1.SortOrder = o2.SortOrder - 1

Step 3 - managing gaps at the start and end of the range

The last step is if a room is empty at the start of the range - this won;t be included in Step 1 as there is no "previous" row for the first contract.

For this, we just need to find the earliest occupied date and use this as the expiry date for an Empty period. We also check this is after the startDate so we don't get entries starting and ending on the same day for a rom which is actually occupied.

The same applied for end date - find the maximum expiry and use endDate as the end:

select id, @startDate as activeon, min(activeon) - 1 as expireson, 'Empty' as [State] from occupieds group by id
    having min(activeon) > @startDate
union all
select id, max(expireson) + 1 as activeon, @endDate as expireson, 'Empty' as [State] from occupieds group by id
    having max(expireson) < @enddate

Step 4 - Put it all together

We have all the entries we need now, so we just union the three result sets together:

select id, activeon, expireson, [State] from occupieds
    union all
select id, activeon, expireson, [State] from empties
    union all
select id, activeon, expireson, [State] from extremes
order by id, activeon

For the empties and extremes CTEs, you can just make them subqueries in the final union, but I separated them for clarity

; with occupieds as (
    SELECT Room.id,
           RentContract.activeon,
           RentContract.expireson,
           'Occupied' as [State],
           row_number() over (partition by roomid order by activeon) SortOrder
    FROM RentContract
    INNER JOIN Room ON RentContract.roomid = Room.id
    WHERE (RentContract.activeon >= @startDate
           OR RentContract.activeon IS NULL)
      AND (RentContract.expireson <= @endDate
           OR RentContract.expireson IS NULL)
)
select id, activeon, expireson, [State] from occupieds
    union all
select o1.id, o1.expireson + 1 as activeon, o2.activeon - 1 as expireson, 'Empty' as [State] from occupieds o1
    inner join occupieds o2 on o1.id = o2.id and o1.SortOrder = o2.SortOrder - 1
    union all
select id, @startDate as activeon, min(activeon) - 1 as expireson, 'Empty' as [State] from occupieds group by id
    having min(activeon) > @startDate
    union all
select id, max(expireson) + 1 as activeon, @endDate as expireson, 'Empty' as [State] from occupieds group by id
    having max(expireson) < @enddate
order by id, activeon