none
Determinar última continuidad de tiempo entre rangos de fechas RRS feed

  • Pregunta

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

    martes, 21 de agosto de 2018 14:04

Respuestas

  • 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

    miércoles, 22 de agosto de 2018 8:53
  • 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

    • Editado HunchbackMVP lunes, 1 de octubre de 2018 19:07
    • Marcado como respuesta Roysb lunes, 1 de octubre de 2018 20:20
    lunes, 1 de octubre de 2018 19:04

Todas las respuestas

  • 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

    miércoles, 22 de agosto de 2018 8:53
  • 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.


    • Editado Roysb lunes, 1 de octubre de 2018 14:57 explicacion mejor
    lunes, 1 de octubre de 2018 14:56
  • 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

    lunes, 1 de octubre de 2018 16:00
  • 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.
    lunes, 1 de octubre de 2018 18:26
  • 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

    • Editado HunchbackMVP lunes, 1 de octubre de 2018 19:07
    • Marcado como respuesta Roysb lunes, 1 de octubre de 2018 20:20
    lunes, 1 de octubre de 2018 19:04