ギャップと島

Oct 16 2020

StartDateとEndDateのテーブルがあります。不足している日付を取得したかったのですが、スクリプトがそれを実行します。しかし、このデータが与えられた場合：

LocationID  StartDate   EndDate
----------  ----------  ----------  
1           2020-01-01  2020-01-03  
1           2020-01-04  2020-01-05  
1           2020-01-10  2020-01-15

このクエリ：

DECLARE @t table(PlaceID int, StartDate date, EndDate date);

INSERT @t(PlaceID, StartDate, EndDate) VALUES
(1,'20200101','20200103'),(1,'20200104','20200105'),(1,'20200110','20200115');

--(2,'20200103','20200106'),(2,'20200107','20200110'),(2,'20200120','20200123');

-- input parameters 
DECLARE @PlaceIDofInterest   int  = 1,
        @StartDateOfInterest date = '20200101', 
        @EndDateOfInterest   date = '20200131';
    
;WITH date_range(d) AS -- the entire range of days we care about
(
  SELECT @StartDateOfInterest UNION ALL
  SELECT DATEADD(DAY, 1, d) FROM date_range
         WHERE d < @EndDateOfInterest
),
islands AS -- grouped sets of days _not_ covered
(
  SELECT r.d, island = DATEADD(DAY, DENSE_RANK() OVER (ORDER BY r.d) * -1, r.d) 
    FROM date_range AS r
    LEFT OUTER JOIN @t AS t
      ON  r.d >= t.StartDate 
      AND r.d <= t.EndDate
      AND t.PlaceID = @PlaceIDofInterest
    WHERE t.PlaceID IS NULL
)
SELECT  
      MIN(d) as STARTDATE , 
      MAX(d) as ENDDATE-- for each island, grab the start and end
  FROM 
      islands 
 
  GROUP BY island 
  ORDER BY MIN(d);

この出力を返します：

STARTDATE   ENDDATE  
---------   ----------    
2020-01-06  2020-01-09  
2020-01-16  2020-01-31

しかし、私は次のような出力が必要でした。

STARTDATE   ENDDATE
---------   ----------    
2020-01-03  2020-01-04  
2020-01-05  2020-01-10  
2020-01-15  2020-01-31

どのような変更を加えることができますか？

追加のために編集：場所は夜から朝まで予約できます。つまり、StartDateNightからEndDateMorningまでです。そのため、予約されていない限り、ロケーションはEndDateの夜に無料になります。また、場所は開始日の朝に無料になります。

1月1日から1月3日までが予約されています。つまり、1日目の夜から3日目の朝までです。次の予約は4泊目のみです。そのため、場所は3日目の夜から4日目の朝まで無料です。したがって、1行に3番目を開始日、4番目を終了日とする結果が必要になります。これが明確であることを願っています。

回答

1 nbk Oct 16 2020 at 20:57

あなたは本当にそのためのcteを必要としません

ウィンドウ関数LEADは必要なことを実行できます

日付が一致していれば、洗練することができます

CREATE table t1(PlaceID int, StartDate date, EndDate date);

GO

INSERT INTO  t1 (PlaceID, StartDate, EndDate) VALUES
(1,'20200101','20200103')
,(1,'20200104','20200111')
,(1,'20200111','20200113')
,(1,'20200114','20200121')
,(1,'20200121','20200123')
,(2,'20200103','20200106')
,(2,'20200107','20200110')
,(2,'20200120','20200123');
GO

SELECT
PlaceID
,EndDate
,Nextdate
FROM
(SELECT
PlaceID
,EndDate,
COALESCE(DATEADD(DAY,0,lead(StartDate,1,NULL)  OVER (partition by PlaceID ORDER BY StartDate)),EOMONTH(StartDate)) AS Nextdate
FROM t1) t2
WHERE EndDate != Nextdate
GO

PlaceID | EndDate | 次の日付  
------：| ：--------- | ：---------
      1 | 2020-01-03 | 2020-01-04
      1 | 2020-01-13 | 2020-01-14
      1 | 2020-01-23 | 2020-01-31
      2 | 2020-01-06 | 2020-01-07
      2 | 2020-01-10 | 2020-01-20
      2 | 2020-01-23 | 2020-01-31

db <>フィドルはこちら

JonathanFite Oct 16 2020 at 21:06

@nbkによる回答は、あなたが望むものを得るでしょう。あなたにとって興味深いことがわかるかもしれないので、私はこれをここに置いています。完全ではありませんが。現時点では、可能な予約日のリストを取得し、概説したルールに従って予約されているかどうかを示します。カレンダービューなどとして役立つ場合があります。