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