CODEWITHSUNDEEP
sqlsql-servert-sql
NoxiaZ
NoxiaZ

Reputation: 43

MS SQL combine Date rows into start end date

I'm looking for a select statement or something that can combine multiple rows when there is only a day between two dates for same room into a single row that have a the start and a end date for such period per room.

It should combine all rows with same RoomID when only one day is between the previous row and instead calculate the Start Date and End Date.

I have tried with group by, but haven't been possible to create anything close to the output.

The table it self contains more then million records, and the code needs to combine all rows between 2 dates, in this scenario it's from 2016-08-01 to 2016-09-03

I hope someone are able to help me solve this issue, thanks.

My data looks like this, and always sorted by RoomID then Date:

ID          RoomID      Date
60307       1164        01-08-2016 00:00
60308       1164        02-08-2016 00:00
60309       1164        03-08-2016 00:00
60310       1164        04-08-2016 00:00
60312       1164        06-08-2016 00:00
60313       1164        07-08-2016 00:00
60314       1165        01-08-2016 00:00
60315       1165        02-08-2016 00:00
60316       1165        03-08-2016 00:00
60317       1165        04-08-2016 00:00
60318       1165        05-08-2016 00:00
60319       1165        06-08-2016 00:00
60320       1165        07-08-2016 00:00
60383       1164        10-08-2016 00:00
60384       1164        11-08-2016 00:00
60385       1164        12-08-2016 00:00
60386       1165        10-08-2016 00:00
60387       1165        11-08-2016 00:00
60388       1165        12-08-2016 00:00
60395       1164        01-09-2016 00:00
60396       1164        02-09-2016 00:00
60397       1164        03-09-2016 00:00
60398       1164        04-09-2016 00:00
60399       1164        05-09-2016 00:00
60400       1165        01-09-2016 00:00
60401       1165        02-09-2016 00:00
60402       1165        03-09-2016 00:00

And combined should look like this:

RoomID      Startdate       EndDate
1164        01-08-2016 00:00        04-08-2016 00:00
1164        06-08-2016 00:00        07-08-2016 00:00
1165        01-08-2016 00:00        07-08-2016 00:00
1164        10-08-2016 00:00        12-08-2016 00:00
1165        10-08-2016 00:00        12-08-2016 00:00
1164        01-09-2016 00:00        05-09-2016 00:00
1165        01-09-2016 00:00        03-09-2016 00:00

Upvotes: 3

Views: 3067

Answers (1)

DhruvJoshi
DhruvJoshi

Reputation: 17126

You can try something like below:

select 
  RoomID,
  MIN([Date]) as StartDate, 
  MAX([Date]) as EndDate 
from 
 (
  Select 
   *, 
   ROW_NUMBER() OVER(PARTITION BY RoomID ORDER BY [Date] asc) as ranking 
   from tbl
 ) t  
 group by 
   RoomID, (CAST([Date] AS INT)-Ranking)
order by 
   RoomID, (CAST([Date] AS INT)-Ranking)

Upvotes: 3

Related Questions