rango - operaciones con fechas sql

T-SQL Calcula la duración en meses entre diferentes años de rangos

Question

Tengo una tabla en SQL Server que contiene la duración de un usuario que trabaja para diferentes trabajos. Necesito calcular la cantidad total de experiencia para el usuario.

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 

Quiero calcular el número total de experiencia;

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

después de eliminar los años que se superponen, como 2003-2005 cubre en 2000-2008; Tengo algo como esto:

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

Entonces la respuesta sería 125+19 = 144 meses. Por favor, ayúdame a encontrar una solución.

Answer 1

La sintaxis aquí es encontrar todo FromDate que no tiene un intervalo FromDate y ToDate superpuesto y todos los ToDates que no tienen un intervalo FromDate y ToDate superpuestos. Dándoles un número de barras de acuerdo con el valor de la fecha y haciendo coincidirlas en ese número de barras:

;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

Resultado:

144

Answer 2

Puedes intentar esto

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

Puedes probar este código:

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

Resultado:

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