Beginner Beginner - 3 years ago 79
SQL Question

Select start date and end date form records with subsequent date field in SQL server 2008

I have a table in SQL SERVER 2008 R2 that is called ReserveLog.
This is an existing table that stores the reserve date of each room in a complex.
It is like this:

RoomNumber ReserveDate
1 2017-07-01
1 2017-07-02
1 2017-07-03
1 2017-07-06
1 2017-07-07
1 2017-07-08
2 2017-01-02
2 2017-01-03
2 2017-01-04
2 2017-01-09
2 2017-01-10


I want to query this table so that I get the following result:

RoomNumber ReserveStartDate ReserveEndDate
1 2017-07-01 2017-07-03
1 2017-07-06 2017-07-08
2 2017-07-02 2017-07-04
2 2017-07-09 2017-07-10


Is it possible? I can't make my mind how to do it. Any help is appreciated in advance

Answer Source
create table #reservs
(
roomnumber INT, ReserveDate DATE
)


INSERT INTO #reservs VALUES (1,          '2017-07-01');
INSERT INTO #reservs VALUES (1,          '2017-07-02');
INSERT INTO #reservs VALUES (1,          '2017-07-03');
INSERT INTO #reservs VALUES (1,          '2017-07-06');
INSERT INTO #reservs VALUES (1,          '2017-07-07');
INSERT INTO #reservs VALUES (1,          '2017-07-08');
INSERT INTO #reservs VALUES (2,          '2017-01-02');
INSERT INTO #reservs VALUES (2,          '2017-01-03');
INSERT INTO #reservs VALUES (2,          '2017-01-04');
INSERT INTO #reservs VALUES (2,          '2017-01-09');
INSERT INTO #reservs VALUES (2,          '2017-01-10');

select roomnumber, MIN(reservedate) as mn, MAX(reservedate) as mx
FROM (
SELECT *
, DATEDIFF(day, ROW_NUMBER() OVER(partition by roomnumber order by reservedate) ,reservedate) as ind
 FROM #reservs
) a
group by roomnumber, ind
order by 1, 2
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download