0

I have one system which stores data like:

CREATE TABLE Reservations
    ([UserId] int, [RoomId] int, [TypeId] int, [Date] datetime)
;

Every row in the table means that Room is reserved for User of some Type in given Date

I need to convert data to this system:

CREATE TABLE Reservations
    ([UserId] int, [RoomId] int, [TypeId] int, [StartDate] datetime, [EndDate] datetime)
;

Or to be more precise, I need to just make select statement for selecting data from old system in a new way. StartDate and EndDate must represent continuous date range.

I prepared this sql fiddle. I tried to write select statement but with no success. What am I missing?

;WITH GRP AS
(
    SELECT 
    *               
    ,ROW_NUMBER() OVER(ORDER BY [Date] ASC)
        -ROW_NUMBER() OVER(PARTITION BY [UserId], [RoomId], [TypeId] ORDER BY [Date] ASC) AS Grp
FROM
    Reservations
)
, MinMax AS
(
    SELECT
        [UserId], [RoomId], [TypeId]
        ,MIN([Date]) AS StartDate
        ,MAX([Date]) AS EndDate
    FROM
        GRP
    GROUP BY
        [UserId], [RoomId], [TypeId]
        ,Grp
)

SELECT
    [UserId], [RoomId], [TypeId]
    ,StartDate  
    ,LEAD(StartDate,1,StartDate) OVER(ORDER BY StartDate) AS EndDate
FROM
    MinMax
ORDER BY
    StartDate
    ,EndDate
0

Your problem is known as an "Islands and Gaps" problem. As far as I can see your solutions is correct (given that I understood what you try to achieve) up until:

,LEAD(StartDate,1,StartDate) OVER(ORDER BY StartDate) AS EndDate

Why are you replacing the EndDate you calculated in MinMax? Try:

SELECT
    [UserId], [RoomId], [TypeId]
    ,StartDate  
    ,EndDate
FROM
    MinMax
ORDER BY
    StartDate
   ,EndDate

Regarding how you calculate the group, I assume that a reservation is for the same room, and type during the whole period. You may want to consider something like:

,ROW_NUMBER() OVER (PARTITION BY [RoomId], [TypeId] 
                    ORDER BY [Date] ASC)
-ROW_NUMBER() OVER (PARTITION BY [UserId], [RoomId], [TypeId] 
                    ORDER BY [Date] ASC) AS Grp

In your query, GRP changes regardless of which room or type that interferes with the enumeration of dates.

PS. Welcome to the forum. A Very good first question. If GRP puzzles you, I suggest that you look at the row_number functions individually, before looking at their difference DS.

EDIT:

The idea you used uses the fact that some other user interrupts the current ordering, and thereby creates a new grp. Since several users can rent the room at the same time, I don't think that will work. Here is one idea (date is a reserved word so I changed it to dt to be able to get rid of the annoying quotes []):

with start_period as (
    select a.userid, a.roomid, a.typeid, a.dt as start_date 
    from Reservations a
    where not exists (
        select 1 from Reservations b
        where a.userid = b.userid
          and a.roomid = b.roomid
          and a.typeid = b.typeid
          and a.dt = dateadd(day, 1, b.dt)
    )
), end_period as (
    select a.userid, a.roomid, a.typeid, a.dt as end_date 
    from Reservations a
    where not exists (
        select 1 from Reservations b
        where a.userid = b.userid
          and a.roomid = b.roomid
          and a.typeid = b.typeid
          and a.dt = dateadd(day, -1, b.dt)
    )
)  
select x.userid, x.roomid, x.typeid, x.start_date
     , min(y.end_date) as end_date
from start_period x
join end_period y
    on x.userid = y.userid
    and x.roomid = y.roomid
    and x.typeid = y.typeid
    and x.start_date <= y.end_date
group by x.userid, x.roomid, x.typeid, x.start_date;

1   1   2   2020-04-01T00:00:00Z    2020-04-03T00:00:00Z
1   1   2   2020-04-05T00:00:00Z    2020-04-07T00:00:00Z
1   1   2   2020-04-10T00:00:00Z    2020-04-10T00:00:00Z
2   1   2   2020-04-01T00:00:00Z    2020-04-03T00:00:00Z
2   1   2   2020-04-05T00:00:00Z    2020-04-05T00:00:00Z
2   1   3   2020-04-06T00:00:00Z    2020-04-07T00:00:00Z

Fiddle

The idea is pretty simple, get all dates (for a given user, room, type) where there is no predecessor. Then get all dates (for a given user, room, type) where there is no successor. For all start_dates, find the smallest end_date that is bigger than the start_date

| improve this answer | |
  • Hello, here is sql fiddle with your update. Results should be different, expected results are in fiddle. – Tom C. Apr 15 at 20:42
  • In your fiddle user 1 and 2 both booked room 1 at the same date, is that possible? – Lennart Apr 15 at 20:56
  • Yes, multiple users can book same room at the same time but with different typeId. I updated sql fiddle – Tom C. Apr 15 at 22:38
  • Then a change of user can't be used as an "interrupt", I'll think about it and eventually update my answer. – Lennart Apr 16 at 4:22
  • @TomC. I have updated my answer – Lennart Apr 16 at 4:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.