MSSQL - 轉換日期範圍內的日期時間列表

我有一個儲存數據的系統，例如：

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

表中的每一行都意味著房間是為給定日期的某些類型的使用者保留的

我需要將數據轉換到這個系統：

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

或者更準確地說，我只需要使用 select 語句以一種新的方式從舊系統中選擇數據。StartDate 和 EndDate 必須代表連續的日期範圍。

我準備了這個sql fiddle。我試圖編寫選擇語句但沒有成功。我錯過了什麼？

;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

您的問題被稱為“島嶼和差距”問題。據我所知，您的解決方案是正確的（鑑於我了解您試圖實現的目標），直到：

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

為什麼要替換在 MinMax 中計算的 EndDate？嘗試：

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

關於如何計算組，我假設預訂是針對同一個房間，並在整個期間鍵入。您可能需要考慮以下內容：

,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

在您的查詢中，無論哪種房間或類型乾擾日期列舉，GRP 都會發生變化。

PS。歡迎來到論壇。一個非常好的第一個問題。如果 GRP 讓您感到困惑，我建議您在查看它們的差異 DS 之前單獨查看 row_number 函式。

編輯：

您使用的想法使用了其他使用者中斷目前排序的事實，從而創建了一個新的 grp。由於多個使用者可以同時租用房間，我認為這行不通。這是一個想法（日期是保留字，所以我將其更改為 dt 以便能夠擺脫煩人的引號

$$ $$):

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

小提琴

這個想法很簡單，獲取沒有前任的所有日期（對於給定的使用者、房間、類型）。然後獲取沒有後繼者的所有日期（對於給定的使用者、房間、類型）。對於所有 start_dates，找到大於 start_date 的最小 end_date

引用自：https://dba.stackexchange.com/questions/264988