T-SQL Calculate duration in months between different years of ranges

Question

I have a table in SQL Server which contains the duration of a user working for different jobs. I need to calculate the total number of experience for user.

Declare @temp table(Id int, FromDate DATETIME, ToDate DATETIME)
INSERT INTO @temp ( Id ,FromDate ,ToDate )
VALUES   ( 1 , '2003-1-08 06:55:56' , '2005-5-08 06:55:56'),
         ( 2 , '2000-10-08 06:55:56' , '2008-7-08 06:55:56'),
         ( 3 , '2013-6-08 06:55:56' , '2015-1-08 06:55:56'),
         ( 4 , '2006-4-08 06:55:56' , '2011-3-08 06:55:56' )
SELECT * FROM @temp 

I want to calculate the total number of experience;

Id  FromDate    ToDate       Difference IN Months
===================================================
1   2003-01-08  2005-05-08    28
2   2000-10-08  2008-07-08    93
3   2013-06-08  2015-01-08    19
4   2006-04-08  2011-03-08    59

after removing the years overlapping like 2003-2005 covers up in 2000-2008; I got something like this:

Id  FromDate    ToDate       Difference IN Months
===================================================    
1   2000-10-08  2011-03-08    125
2   2013-06-08  2015-01-08    19

So the answer would be 125+19 = 144 Months. Please help me to find a solution.

Answer 1

The syntax here is finding all FromDate that doesn't have an overlapping FromDate and ToDate interval and all ToDates that doesn't have an overlapping FromDate and ToDate interval. Giving them a rownumber according to the date value and matching them on that rownumber:

;WITH CTE as
(
  SELECT min(Id) Id ,FromDate, row_number() over (ORDER BY FromDate) rn
  FROM @temp x
  WHERE 
    not exists
      (SELECT * FROM @temp WHERE x.FromDate > FromDate and x.FromDate <= Todate)
  GROUP BY FromDate
), CTE2 as
(
  SELECT Max(Id) Id ,ToDate, row_number() over (ORDER BY ToDate) rn
  FROM @temp x
  WHERE
    not exists
      (SELECT * FROM @temp WHERE x.ToDate >= FromDate and x.ToDate < Todate)
  GROUP BY ToDate
)
SELECT SUM(DateDiff(month, CTE.FromDate, CTE2.ToDate))
FROM CTE
JOIN CTE2
ON CTE.rn = CTE2.rn

Result:

144

Answer 2

You can try this

SELECT Set1.FromDate,MIN(List1.ToDate) AS ToDate, DATEDIFF(MONTH,Set1.FromDate,MIN(List1.ToDate))
    FROM @temp Set1 
    INNER JOIN @temp List1 ON Set1.FromDate <= List1.ToDate
    AND NOT EXISTS(SELECT * FROM @temp List2 

    WHERE List1.ToDate >= List2.FromDate AND List1.ToDate < List2.ToDate) 
    WHERE NOT EXISTS(SELECT * FROM @temp Set2 
    WHERE Set1.FromDate > Set2.FromDate AND Set1.FromDate <= Set2.ToDate) 

    GROUP BY Set1.FromDate 
    ORDER BY Set1.FromDate 

Answer 3

You can try this code:

DECLARE @temp TABLE (ID INT, FromDate DATETIME, ToDate DATETIME)
INSERT INTO @temp (ID, FromDate, ToDate)
VALUES   ( 1 , '2003-1-08 06:55:56' , '2005-5-08 06:55:56'),
         ( 2 , '2000-10-08 06:55:56' , '2008-7-08 06:55:56'),
         ( 3 , '2013-6-08 06:55:56' , '2015-1-08 06:55:56'),
         ( 4 , '2006-4-08 06:55:56' , '2011-3-08 06:55:56' )
SELECT 
    ID,
    CONVERT(DATE, FromDate) AS FromDate,
    CONVERT(DATE, ToDate) AS ToDate,
    DATEDIFF(MONTH, FromDate, ToDate) AS [Difference IN Months]
INTO #tmp
FROM @temp

SELECT T1.ID AS ID1, T2.ID AS ID2, T2.ToDate, T1.[Difference IN Months] + T2.[Difference IN Months] AS [Difference IN Months]
INTO #tmp2
FROM #tmp T1
INNER JOIN #tmp T2
    ON CAST(T1.ToDate AS DATE) = CAST(T2.FromDate AS DATE)
        OR (YEAR(T1.ToDate) = YEAR(T2.FromDate) AND CAST(T1.ToDate AS DATE) < CAST(T2.FromDate AS DATE))
        OR YEAR(T1.ToDate) = YEAR(T2.FromDate) - 1
DELETE #tmp WHERE ID IN (SELECT ID2 FROM #tmp2)
UPDATE #tmp
SET ToDate = (SELECT ToDate FROM #tmp2 WHERE #tmp.ID = ID1),
    [Difference IN Months] = (SELECT [Difference IN Months] FROM #tmp2 WHERE #tmp.ID = ID1)
WHERE ID IN (SELECT ID1 FROM #tmp2)

SELECT
    *,
    ROW_NUMBER() OVER(ORDER BY FromDate) AS RF
INTO #tmp3
FROM #tmp

SELECT T1.ID AS ID1, T2.ID AS ID2, T1.ToDate
INTO #tmp4
FROM #tmp3 T1
    INNER JOIN #tmp3 T2 ON T1.RF = T2.RF + 1
WHERE CAST(T1.FromDate AS DATE) < CAST(T2.ToDate AS DATE)

UPDATE #tmp
SET ToDate = (SELECT ToDate FROM #tmp4 WHERE #tmp.ID = ID2)
WHERE ID IN (SELECT ID2 FROM #tmp4)
DELETE #tmp WHERE ID IN (SELECT ID1 FROM #tmp4)

UPDATE #tmp
SET[Difference IN Months] = DATEDIFF(MONTH, FromDate, ToDate)

SELECT
    ROW_NUMBER() OVER(ORDER BY FromDate) AS ID,
    FromDate, ToDate, [Difference IN Months]
FROM #tmp

DROP TABLE #tmp
DROP TABLE #tmp2
DROP TABLE #tmp3
DROP TABLE #tmp4

Result:

ID  FromDate    ToDate       Difference IN Months
===================================================    
1   2000-10-08  2011-03-08    125
2   2013-06-08  2015-01-08    19