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.
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