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?

it would be better if you post table data with expected output — bmsqldev
– bmsqldev, Commented Jun 2, 2016 at 8:57
Start with a list of contiguous dates from a CTE, date table etc then LEFT JOIN to your query. Add copious COALECSE or IFNULL for your state labels and bake... — Liesel
– Liesel, Commented Jun 2, 2016 at 9:17
Does RentContract has both columns like activeon and new_activeon? If yes, how are they different? Also, what is the criteria to decide intervals, specially the ones you want to show dynamically? There has to be some logic on which you want to generate them. And yes, please show some sample data in tables. Thanks. — Akanksha Singh
– Akanksha Singh, Commented Jun 2, 2016 at 9:54

Rhumborl · Accepted Answer · 2016-06-02 14:31:49Z

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

that looks very nice. I will check out and come back to you. Thanks a lot
Thanks a lot four your help. I have a question on one detail, please check my edit.
Sorry, small bug in the last section. We just need to check if the min date is greater than @startDate not +/- 1 day. Updated.

gofr1 · Accepted Answer · 2016-06-02 09:51:17Z

3

You can do it with the help of cte. As you haven't provided more data - will work with what is posted:

DECLARE @startdate date = '2016-05-01',
        @enddate date = '2016-07-01'

;WITH cte AS (
    SELECT r.id,
           rc.activeon,
           rc.expireson,
           'Occupited' as [state]
    FROM RentContract rc
    INNER JOIN Room r
        ON rc.roomid = r.id
    WHERE (rc.new_activeon >= @startDate OR rc.new_activeon IS NULL)
      AND (rc.new_expireson <= @endDate OR rc.new_expireson IS NULL)
)


SELECT *
FROM cte
UNION ALL
SELECT  id, 
        activeon, 
        CASE WHEN expireson < activeon THEN @enddate ELSE expireson END as expireson,
        'Empty' as [state]
FROM (
    SELECT id, CAST(DATEADD(day,1,expireson) as date) as activeon, ROW_NUMBER() OVER (ORDER BY expireson ASC) as rn
    FROM cte
    ) as s
INNER JOIN (
    SELECT CAST(DATEADD(day,-1,activeon)as date) as expireson, ROW_NUMBER() OVER (ORDER BY expireson ASC) as rn
    FROM cte
    ) as e
    ON e.rn=s.rn+1
ORDER BY id, activeon

Output:

id  activeon    expireson   state
1   2016-05-01  2016-05-31  Occupited
1   2016-06-01  2016-06-14  Empty
1   2016-06-15  2016-06-25  Occupited
1   2016-06-26  2016-07-01  Empty
2   2016-05-01  2016-07-01  Occupited

answered Jun 2, 2016 at 9:51

gofr1

16k11 gold badges47 silver badges56 bronze badges

1 Comment

user1016077 Over a year ago

that looks nice. I will check out and come back to you. Thanks a lot

Collectives™ on Stack Overflow

T-SQL adding dynamic rows into resultset

2 Answers 2

Step 1 - Occupied Rooms

Step 2 - empty rooms between contracts

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

Step 4 - Put it all together

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Step 1 - Occupied Rooms

Step 2 - empty rooms between contracts

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

Step 4 - Put it all together

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related