CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012sqldatetime
Umer Farooqui
Umer Farooqui

Reputation: 307

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

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.

Upvotes: 9

Views: 293

Answers (3)

Nguyễn Hải Triều
Nguyễn Hải Triều

Reputation: 1464

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

Upvotes: 1

HiteshAjudiya
HiteshAjudiya

Reputation: 137

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

Upvotes: 3

t-clausen.dk
t-clausen.dk

Reputation: 44326

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

Upvotes: 7

Related Questions