Find total duration of a series of date intervals

Question

I have a database with a list of jobs; for instance, it could be the list of previous work experiences in a CV. I would like to know how many days a person has been employed; so, I wrote this query:

SELECT sum(datediff(dd,isnull(date_begin,getdate()),isnull(date_end,getdate()))+1)
  FROM jobs
 WHERE person_id=X

Problem is, sometimes these dates overlap. Example:

person date_from   date_to     Comment
John   01-Jan-2011 31-Dec-2012 Two years working at ABC
John   01-Jan-2012 31-Dec-2013 Two years of extra work at evening

In this case my query would return 4 years, but I would like to return only 3. In other words, if a person has 2 jobs for one year I want it to count as 1 year, not as 2 years. The overlapping jobs could be any number, not just 2. I tried adjusting the query by subtracting the total duration of all the overlaps, which would work well with 2 overlapping jobs, but this would fail if there are more.

While the solution could be in any language, my plan is to make an SQL function, so I would prefer pure SQL. — carlo.borreo, Aug 30 '13 at 16:44
which sql engine do you use? Mysql? SQL Server? Might be complicated in sql only. — csg, Aug 30 '13 at 16:44

Hart CO · Accepted Answer · 2013-08-31T13:08:12.337

You can use a cte to make a driver list of dates, then JOIN to that using BETWEEN to get distinct days, if this query gets run frequently it's probably worth using the cte to create a date lookup table and indexing it:

;WITH cte AS (SELECT CAST('20100101' AS DATE) 'Dt'
              UNION ALL
              SELECT DATEADD(DAY,1,Dt)
              FROM cte
              WHERE dt <= GETDATE())   
SELECT person,MIN(b.dt),MAX(b.Dt),COUNT(DISTINCT b.dt)'Total Days'
FROM #Table a
JOIN cte b
  ON b.Dt BETWEEN a.date_from AND a.date_to            
GROUP BY person
OPTION (MAXRECURSION 0)

score 1 · Answer 2 · edited May 23 '17 at 12:29

One way I could think of would be to create a temp table to hold each day worked between the start and end dates. You would then create a cursor to loop through each record for the employee (each unique employment entry) and add all days worked to the temp table (an example of Get a list of dates between two dates using a function).

Once you have the table loaded with all days worked, select a count of distinct days in the list.

Find total duration of a series of date intervals

2 Answers2