SQL calculate date segments within calendar year

Question

What I need is to calculate the missing time periods within the calendar year given a table such as this in 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

Expected return would be:

 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

It's essentially work blocks. What I need to show is the part of the calendar year which was NOT worked. So for ID = 3, he worked from 3/1 through the rest of the year. But he did not work from 1/1 till 2/28. That's what I'm looking for.

Answer 1

You can do it using LEAD, LAG window functions available from 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

The idea behind the above query is simple: for every [DateStart, DateEnd] interval get 'not worked' interval right before it. If there is no interval following the current interval, then also get successive 'not worked' interval (if any).

Also note that I assume that if DateStart is NULL then DateStart is also NULL for the same ID.

Demo here