Determinar última continuidad de tiempo entre rangos de fechas

Question

Hola compañeros del foro, solicito de su colaboración para definir una funcion que me devuelva la fecha de inicio del último periodo continuo de una persona de una serie de registros de rangos de fecha, por ejemplo de tabla:

ID_PERSONA, FECHA_INICIO, FECHA_FINAL

3, 2001/01/01, 2003/05/15

3, 2003/07/01, 2004/10/30

3, 2005/02/01, 2008/08/10

3, 2008/08/11, 2011/03/20

3, 2011/03/21, 2017/05/10

del ejemplo anterior se deberia determinar que la fecha de inicio de la tercera fila (2005/02/01) es el inicio del ultimo periodo continuo hasta la ultima fecha final registrado pues apartir de esa fila la fechas finales y de inicio entre registros es continuo.

Espero haberme explicado bien, y desde ya agradezco el aporte o ayuda que me puedan dar.

Answer 1

Hola Roysb:

Vaya por delante, que yo no he entendido de todo cual es tu necesidad, no obstante, bien sea por aportar un granito, o porque puedas mejorar la definición, te pongo algo que quizá te ayude.

CREATE TABLE rangos
(id_persona   INT,
 fecha_inicio DATE,
 fecha_fin    DATE
);
GO
INSERT INTO rangos
(id_persona,
 fecha_inicio,
 fecha_fin
)
VALUES
(3, '20010101', '20030515'),
(3, '20030701', '20041030'),
(3, '20050101', '20080810'),
(3, '20080101', '20110320'),
(3, '20110101', '20170510');
GO

WITH cte
     AS (
     SELECT ROW_NUMBER() OVER(PARTITION BY id_persona ORDER BY id_persona) AS fila,
            id_persona,
            fecha_inicio,
            fecha_fin,
            DATEDIFF(day, fecha_inicio, fecha_fin) AS dias
     FROM rangos)
     SELECT DISTINCT
            c.id_persona,
            c.fecha_inicio,
            c.fecha_fin,
            c.dias,
            d.fila,
            DATEDIFF(day, d.fecha_inicio, c.fecha_fin) AS rango
     FROM cte AS c
          INNER JOIN cte AS d ON c.id_persona = d.id_persona
                                 AND c.fila = d.fila + 1;

Como no he entendido del todo la info, te he puesto el registro con su siguiente, y de ellos extraigo el rango de fechas, llamado días, entre el inicio y el fin, o entre el fin y su inicio siguiente.

Espero te aporte. Ya comentas.

Un saludo

Answer 2

Muchas gracias Javier Fernandez, por tomarse la molestia de analizar y responder, no es la respuesta que ando buscando precisamente, voy tratar de explicarme mejor, esta es una tabla de registros de tiempo laboral de los empleados en instituciones públicas, con base al ejemplo que expuse anteriormente el empleado con ID= 3, ha laborado en 5 ocaciones en varias instituciones publicas, lo que requiero es que por cada empleado determinar la fecha de inicio de su ultimo periodo continuo (que no ha interrumpido su relación laboral con el sector público aunque haya trabajado en diferentes instituciones de gobierno), en el ejemplo que puse, la fecha de inicio de la 3 fila seria la fecha de inicio del periodo continuo pues apesar de que tiene otros periodos registrados, son periodos continuos (a la fecha final del periodo seguidamente al siguiente dia iniciaba periodo).

Otro ejemplo, entre las filas 1,2 y 3 no hay continuidad pues entre la fecha final y fecha incial del siguiente periodo, hay dias o meses de diferencia, caso que no pasa con las filas 3,4 y 5.

Espero haberme explicado mejor y agradezco tu interes.

Answer 3

Cual version de SQL Server usas?

Desde la version 2012 contamos con funciones de ventanas y off-set que pueden ser de ayuda para resolver este problema.

1 - Identificar filas que no son consecutivas respecto a la anterior (marcar cero o uno)

2 - Sumar la marca anterior por cada particion de ID de persona para encontrar grupos

3 - Identificar el ultimo grupo por cada persona

DECLARE @rangos table (
id_persona   INT,
fecha_inicio DATE,
fecha_fin    DATE
);

INSERT INTO @rangos (
	id_persona,
	fecha_inicio,
	fecha_fin
	)
VALUES
	(3, '20010101', '20030515'),
	(3, '20030701', '20041030'),
	(3, '20050101', '20080810'),
	(3, '20080811', '20110320'),
	(3, '20110321', '20170510'),
	
	(4, '20180101', '20180131'),
	(4, '20180301', '20180430'),
	(4, '20180501', '20180531'),
	(4, '20180701', '20180831');

-- identificar con 1 fila no continua
WITH R1 AS (
SELECT
	*,
	CASE
	WHEN DATEDIFF(DAY, LAG(fecha_fin) OVER(PARTITION BY id_persona ORDER BY fecha_inicio), fecha_inicio) = 1 THEN 0 
	ELSE 1 
	END AS grp_helper
FROM
	@rangos
)
-- sumar bandera desde principio de particion hasta fila corriente
-- para identificar grupos
, R2 AS (
SELECT
	*,
	SUM(grp_helper) OVER(
	PARTITION BY id_persona
	ORDER BY fecha_inicio
	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
	) AS grp
FROM
	R1
)
-- por cada grupo calcular la fecha de inicio minima y la final maxima
, R3 AS (
SELECT
	id_persona,
	MIN(fecha_inicio) AS fecha_inicio,
	MAX(fecha_fin) AS fecha_fin
FROM
	R2
GROUP BY
	id_persona,
	grp
)
-- traer ultimo grupo por cada id de persona
SELECT TOP (1) WITH TIES
	id_persona,
    fecha_inicio,
    fecha_fin
FROM
	R3
ORDER BY
	ROW_NUMBER() OVER(PARTITION BY id_persona ORDER BY R3.fecha_inicio DESC);
GO

---

AMB

Some guidelines for posting questions...

AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

Answer 4

Hola Hunchback, muchas gracias por tu respuesta, es muy interesante e ingeniosa, los datos que usaste es un ejemplo vivo de los datos que tengo que analizar, y logras resolver mi problema, del script, no domino mucho la sintaxis, pero voy a entrar a analizarla paso a paso en la version developer 2014 de SQL server, el problema es que donde esta la BD de producción es SQL server 2008 R2 y en esa versión la funcion LAG() no existe.

Answer 5

Por eso es importante decir desde un comienzo con que version de SQL Server trabajas.

Una posible solucion para la version 2008 R2 es expandir las fechas de cada intervalo si es que el numero de filas, al igual que los intervalos, no son muchas.

/****** Object:  UserDefinedFunction [dbo].[ufn_GetNums]    Script Date: 10/1/2018 3:02:10 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- Itzik's VATN
CREATE FUNCTION [dbo].[ufn_GetNums](@low AS bigint, @high AS bigint) 
RETURNS table
AS
RETURN (
WITH
L0 AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum FROM L5)
SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n
FROM Nums
ORDER BY rownum
);

GO
DECLARE @rangos table (
id_persona   INT,
fecha_inicio DATE,
fecha_fin    DATE
);

INSERT INTO @rangos (
	id_persona,
	fecha_inicio,
	fecha_fin
	)
VALUES
	(3, '20010101', '20030515'),
	(3, '20030701', '20041030'),
	(3, '20050101', '20080810'),
	(3, '20080811', '20110320'),
	(3, '20110321', '20170510'),
	
	(4, '20180101', '20180131'),
	(4, '20180301', '20180430'),
	(4, '20180501', '20180531'),
	(4, '20180701', '20180831');

-- version 2008
-- expandir intervalo
WITH R0 AS (
SELECT
	R.id_persona,
    DATEADD(DAY, N.n, R.fecha_inicio) AS dt
FROM
	@rangos AS R
	CROSS APPLY
	dbo.ufn_GetNums(0, DATEDIFF(DAY, R.fecha_inicio, R.fecha_fin)) AS N
)
-- identificar fechas continuas (islands)
, R1 AS (
SELECT
	id_persona,
	dt,
	DATEADD(DAY, -1 * ROW_NUMBER() OVER(PARTITION BY id_persona ORDER BY dt), dt) AS grp
FROM
	R0
)
-- agrupar y calcular min / max de fechas por cada grupo
, R2 AS (
SELECT
	id_persona,
	MIN(dt) AS fecha_inicio,
	MAX(dt) AS fecha_fin
FROM
	R1
GROUP BY
	id_persona,
	grp
)
, R3 AS (
SELECT
	id_persona,
    fecha_inicio,
    fecha_fin,
	ROW_NUMBER() OVER(PARTITION BY id_persona ORDER BY fecha_inicio DESC) AS rn
FROM
	R2
)
-- traer ultimo grupo por cada id de persona
SELECT
	id_persona,
    fecha_inicio,
    fecha_fin
FROM
	R3
WHERE
	rn = 1
GO

---

AMB

Some guidelines for posting questions...

AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas