SQL calculates date segments for a calendar year

Question

I need to calculate the missing time periods during the calendar year, given the following table 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:

  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 

These are essentially working blocks. What I need to show is part of a calendar year that did NOT work. Thus, for ID = 3, he worked from 3/1 until the end of the year. But he did not work from 1/1 to 2/28. This is what I am looking for.

Answer 1

You can do this using the 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 of ​​the above query is simple: for each interval [DateStart, DateEnd] we get a "non-working" interval right in front of it. If the interval following the current interval is missing, then also get a consecutive "idle" interval (if any).

Also note that I am assuming that if DateStart is NULL , then DateStart also NULL for the same ID .

Demo here

Answer 2

If your data is not too large, this approach will work. It extends all days and identifiers, and then re-groups them:

 with d as ( select cast('2015-01-01' as date) union all select dateadd(day, 1, d) from d where d < cast('2015-12-31' as date) ), td as ( select * from d cross join (select distinct id from t) t where not exists (select 1 from t t2 where dd between t2.startdate and t2.enddate ) ) select id, min(d) as startdate, max(d) as enddate from (select td.*, dateadd(day, - row_number() over (partition by id order by d), d) as grp from td ) td group by id, grp order by id, grp; 

An alternative method is based on aggregate amounts and similar functions, which are much easier to express in SQL Server 2012+.

Answer 3

A slightly simpler approach, I think.

Basically, create a list of dates for all ranges of work blocks (A). Then create a list of dates for the whole year for each ID (B). Then remove A from B. Make the remaining list of dates in the date ranges for each identifier.

 DECLARE @startdate DATETIME, @enddate DATETIME SET @startdate = '2015-01-01' SET @enddate = '2015-12-31' --Build date ranges from remaining date list ;WITH dateRange(ID, dates, Grouping) AS ( SELECT dt1.id, dt1.Dates, dt1.Dates + row_number() over (order by dt1.id asc, dt1.Dates desc) AS Grouping FROM ( --Remove (A) from (B) SELECT distinct dt.ID, tmp.Dates FROM DatesTable dt CROSS APPLY ( --GET (B) here SELECT DATEADD(DAY, number, @startdate) [Dates] FROM master..spt_values WHERE type = 'P' AND DATEADD(DAY, number, @startdate) <= @enddate ) tmp left join ( --GET (A) here SELECT DISTINCT T.Id, D.Dates FROM DatesTable AS T INNER JOIN master..spt_values as N on N.number between 0 and datediff(day, T.DateStart, T.DateEnd) CROSS APPLY (select dateadd(day, N.number, T.DateStart)) as D(Dates) WHERE N.type ='P' ) dr ON dr.Id = dt.Id and dr.Dates = tmp.Dates WHERE dr.id is null ) dt1 ) SELECT ID, CAST(MIN(Dates) AS DATE) DateStart, CAST(MAX(Dates) AS DATE) DateEnd FROM dateRange GROUP BY ID, Grouping ORDER BY ID 

Here is the code: http://sqlfiddle.com/#!3/f3615/1

Hope this helps!