SQL 计算日历年内的日期段

Question

我需要的是计算日历年中缺少的时间段，给定 SQL 中的如下表:

DatesTable
|ID|DateStart   |DateEnd   |
 1  NULL         NULL
 2  2015-1-1     2015-12-31
 3  2015-3-1     2015-12-31
 4  2015-1-1     2015-9-30
 5  2015-1-1     2015-3-31
 5  2015-6-1     2015-12-31
 6  2015-3-1     2015-6-30
 6  2015-7-1     2015-10-31

预期返回为:

 1  2015-1-1     2015-12-31
 3  2015-1-1     2015-2-28
 4  2015-10-1    2015-12-31
 5  2015-4-1     2015-5-31
 6  2015-1-1     2015-2-28
 6  2015-11-1    2015-12-31

它本质上是工作块。我需要展示的是日历年中没有工作的部分。因此，对于 ID = 3，他从 3/1 工作到今年剩余时间。但是他从 1/1 到 2/28 都没有工作。这就是我要找的。

Answer 1

你可以使用 LEAD , LAG SQL Server 2012+ 提供的窗口函数:

;WITH CTE AS (
   SELECT ID, 
          LAG(DateEnd) OVER (PARTITION BY ID ORDER BY DateEnd) AS PrevEnd,
          DateStart,
          DateEnd,
          LEAD(DateStart) OVER (PARTITION BY ID ORDER BY DateEnd) AS NextStart
   FROM DatesTable
)
SELECT ID, DateStart, DateEnd
FROM (
-- Get interval right before current [DateStart, DateEnd] interval
SELECT ID, 
       CASE 
          WHEN DateStart IS NULL THEN '20150101'
          WHEN DateStart > start THEN start
          ELSE NULL
       END AS DateStart,
       CASE 
          WHEN DateStart IS NULL THEN '20151231'
          WHEN DateStart > start THEN DATEADD(d, -1, DateStart)
          ELSE NULL
       END AS DateEnd
FROM CTE
CROSS APPLY (SELECT COALESCE(DATEADD(d, 1, PrevEnd), '20150101')) x(start)

-- If there is no next interval then get interval right after current 
-- [DateStart, DateEnd] interval (up-to end of year)
UNION ALL

SELECT ID, DATEADD(d, 1, DateEnd) AS DateStart, '20151231' AS DateEnd       
FROM CTE
WHERE DateStart IS NOT NULl    -- Do not re-examine [Null, Null] interval
      AND NextStart IS NULL    -- There is no next [DateStart, DateEnd] interval 
      AND DateEnd < '20151231' -- Current [DateStart, DateEnd] interval  
                               -- does not terminate on 31/12/2015
) AS t
WHERE t.DateStart IS NOT NULL
ORDER BY ID, DateStart

上述查询背后的想法很简单:对于每个 [DateStart, DateEnd]间隔在它之前得到“不工作”间隔。如果当前间隔之后没有间隔，则还可以获得连续的“未工作”间隔(如果有)。

另请注意，我假设如果 DateStart是 NULL然后 DateStart也是 NULL对于相同的 ID .

Demo here