none
Agrupar consulta por fecha labor y empresa RRS feed

  • Pregunta

  • Buenas Tardes tengo este inconveniente estoy agrupando por empresa y obteniendo un resumen por trabajador pero me sale todo el detalle y no me agrupa pero si le quito el campo fecha_labor si agrupa pero no considera cronología de fecha de labor. aquí les dejo mi consulta sql

    aquí les dejo la tabla mas simplificada

    COD_EMPRESA FECHA_LABOR TH
    1 3/10/2018 8
    1 4/10/2018 8
    1 5/10/2018 8
    1 6/10/2018 8
    1 9/10/2018 8
    1 10/10/2018 8
    1 11/10/2018 8
    1 12/10/2018 8
    1 13/10/2018 8
    3 31/10/2018 8
    1 1/11/2018 8
    1 2/11/2018 8

    Resultado deseado:

    COD_EMPRESA FechaLaborMin FechaLaborMax TH
    1 3/10/2018 13/10/2018 72
    3 31/10/2018 31/10/2018 8
    1 1/11/2018 2/11/2018 16


    sábado, 3 de noviembre de 2018 22:29

Respuestas

  • Hola FranciscoRiver:

    Puedes extraer el mes de ultima_fecha y utilizarlo con una función de ventana, por año, mes y cod_empresa.

    DECLARE @t TABLE
    (cod_empresa     INT,
     razon_social    VARCHAR(20),
     anno            INT,
     fecha_Labor     DATE,
     primera_labor   DATE,
     ultima_Fecha    DATE,
     dias_trabajados INT,
     total_horas     INT,
     promedio_horas  INT,
     importe_Total   INT
    );
    INSERT INTO @t
    (cod_empresa,
     razon_social,
     anno,
     fecha_Labor,
     primera_labor,
     ultima_Fecha,
     dias_trabajados,
     total_horas,
     promedio_horas,
     importe_Total
    )
    VALUES
    (1,
     'NORTE',
     2018,
     '3/10/2018',
     '3/10/2018',
     '3/10/2018',
     1,
     8,
     8,
     0
    ),
    (1,
     'NORTE',
     2018,
     '4/10/2018',
     '4/10/2018',
     '4/10/2018',
     1,
     8,
     8,
     0
    ),
    (1,
     'NORTE',
     2018,
     '5/10/2018',
     '5/10/2018',
     '5/10/2018',
     1,
     8,
     8,
     0
    ),
    (1,
     'NORTE',
     2018,
     '6/10/2018',
     '6/10/2018',
     '6/10/2018',
     1,
     8,
     8,
     0
    ),
    (1,
     'NORTE',
     2018,
     '9/10/2018',
     '9/10/2018',
     '9/10/2018',
     1,
     8,
     8,
     0
    ),
    (1,
     'NORTE',
     2018,
     '10/10/2018',
     '10/10/2018',
     '10/10/2018',
     1,
     8,
     8,
     0
    ),
    (1,
     'NORTE',
     2018,
     '11/10/2018',
     '11/10/2018',
     '11/10/2018',
     1,
     8,
     8,
     0
    ),
    (1,
     'NORTE',
     2018,
     '12/10/2018',
     '12/10/2018',
     '12/10/2018',
     1,
     8,
     8,
     0
    ),
    (1,
     'NORTE',
     2018,
     '13/10/2018',
     '13/10/2018',
     '13/10/2018',
     1,
     8,
     8,
     0
    ),
    (3,
     'SUR',
     2018,
     '31/10/2018',
     '31/10/2018',
     '31/10/2018',
     1,
     8,
     8,
     40
    ),
    (1,
     'NORTE',
     2018,
     '1/11/2018',
     '1/11/2018',
     '1/11/2018',
     1,
     8,
     8,
     0
    ),
    (1,
     'NORTE',
     2018,
     '2/11/2018',
     '2/11/2018',
     '2/11/2018',
     1,
     8,
     8,
     0
    );
    WITH cte
         AS (
         SELECT 
                MONTH(t.ultima_fecha) AS mes,
                t.cod_empresa,
                t.razon_social,
                t.anno,
                t.fecha_Labor,
                t.primera_labor,
                t.ultima_Fecha,
                t.dias_trabajados,
                t.total_horas,
                t.promedio_horas,
                t.importe_Total
         FROM @t t)
         SELECT c.cod_empresa,
                MIN(c.fecha_labor) AS FechaLaborMin,
                MAX(c.fecha_labor) AS FechaLaborMax,
                SUM(sum(c.total_horas)) OVER(PARTITION BY c.anno,
                                                     c.mes,
                                                     c.cod_empresa) AS TH
         FROM cte c
         GROUP BY C.cod_empresa,
                  C.anno,
                  C.MES,
                  C.total_horas;
    Salida:

    Espero te ayude


    lunes, 5 de noviembre de 2018 6:33
  • ok gracias. Lo probe esta funcional

    Saludos


    lunes, 5 de noviembre de 2018 19:13

Todas las respuestas

  • Hola FranciscoRiver:

    Puedes extraer el mes de ultima_fecha y utilizarlo con una función de ventana, por año, mes y cod_empresa.

    DECLARE @t TABLE
    (cod_empresa     INT,
     razon_social    VARCHAR(20),
     anno            INT,
     fecha_Labor     DATE,
     primera_labor   DATE,
     ultima_Fecha    DATE,
     dias_trabajados INT,
     total_horas     INT,
     promedio_horas  INT,
     importe_Total   INT
    );
    INSERT INTO @t
    (cod_empresa,
     razon_social,
     anno,
     fecha_Labor,
     primera_labor,
     ultima_Fecha,
     dias_trabajados,
     total_horas,
     promedio_horas,
     importe_Total
    )
    VALUES
    (1,
     'NORTE',
     2018,
     '3/10/2018',
     '3/10/2018',
     '3/10/2018',
     1,
     8,
     8,
     0
    ),
    (1,
     'NORTE',
     2018,
     '4/10/2018',
     '4/10/2018',
     '4/10/2018',
     1,
     8,
     8,
     0
    ),
    (1,
     'NORTE',
     2018,
     '5/10/2018',
     '5/10/2018',
     '5/10/2018',
     1,
     8,
     8,
     0
    ),
    (1,
     'NORTE',
     2018,
     '6/10/2018',
     '6/10/2018',
     '6/10/2018',
     1,
     8,
     8,
     0
    ),
    (1,
     'NORTE',
     2018,
     '9/10/2018',
     '9/10/2018',
     '9/10/2018',
     1,
     8,
     8,
     0
    ),
    (1,
     'NORTE',
     2018,
     '10/10/2018',
     '10/10/2018',
     '10/10/2018',
     1,
     8,
     8,
     0
    ),
    (1,
     'NORTE',
     2018,
     '11/10/2018',
     '11/10/2018',
     '11/10/2018',
     1,
     8,
     8,
     0
    ),
    (1,
     'NORTE',
     2018,
     '12/10/2018',
     '12/10/2018',
     '12/10/2018',
     1,
     8,
     8,
     0
    ),
    (1,
     'NORTE',
     2018,
     '13/10/2018',
     '13/10/2018',
     '13/10/2018',
     1,
     8,
     8,
     0
    ),
    (3,
     'SUR',
     2018,
     '31/10/2018',
     '31/10/2018',
     '31/10/2018',
     1,
     8,
     8,
     40
    ),
    (1,
     'NORTE',
     2018,
     '1/11/2018',
     '1/11/2018',
     '1/11/2018',
     1,
     8,
     8,
     0
    ),
    (1,
     'NORTE',
     2018,
     '2/11/2018',
     '2/11/2018',
     '2/11/2018',
     1,
     8,
     8,
     0
    );
    WITH cte
         AS (
         SELECT 
                MONTH(t.ultima_fecha) AS mes,
                t.cod_empresa,
                t.razon_social,
                t.anno,
                t.fecha_Labor,
                t.primera_labor,
                t.ultima_Fecha,
                t.dias_trabajados,
                t.total_horas,
                t.promedio_horas,
                t.importe_Total
         FROM @t t)
         SELECT c.cod_empresa,
                MIN(c.fecha_labor) AS FechaLaborMin,
                MAX(c.fecha_labor) AS FechaLaborMax,
                SUM(sum(c.total_horas)) OVER(PARTITION BY c.anno,
                                                     c.mes,
                                                     c.cod_empresa) AS TH
         FROM cte c
         GROUP BY C.cod_empresa,
                  C.anno,
                  C.MES,
                  C.total_horas;
    Salida:

    Espero te ayude


    lunes, 5 de noviembre de 2018 6:33
  • Ok gracias, por su respuesta.
    Tengo este caso, agregado dos fechas mas en el mes de setiembre pero me sale el siguiente resultado, por lo que veo el agrupamiento lo hace por mes.

    cod_empresa FechaLaborMin FechaLaborMax TH
    1 01/09/2018 03/09/2018 16
    1 03/10/2018 13/10/2018 72
    3 31/10/2018 31/10/2018 8
    1 01/11/2018 02/11/2018 16

    Como podrìa ser, si quiero este resultado, sin tener en cuenta el mes. solo agrupe por empresa y obtenga la primera fecha que labora y la ultima fecha segùn el orden de la empresa donde laborò.

    cod_empresa FechaLaborMin FechaLaborMax TH
    1 01/09/2018 13/10/2018 88
    3 31/10/2018 31/10/2018 8
    1 01/11/2018 02/11/2018 16


    aquí dejo la consulta con las dos fechas nuevas agregadas

    DECLARE @t TABLE
    (cod_empresa     INT,
     razon_social    VARCHAR(20),
     anno            INT,
     fecha_Labor     DATE,
     primera_labor   DATE,
     ultima_Fecha    DATE,
     dias_trabajados INT,
     total_horas     INT,
     promedio_horas  INT,
     importe_Total   INT
    );
    INSERT INTO @t
    (cod_empresa,
     razon_social,
     anno,
     fecha_Labor,
     primera_labor,
     ultima_Fecha,
     dias_trabajados,
     total_horas,
     promedio_horas,
     importe_Total
    )
    VALUES
    (1,'NORTE',2018,'1/09/2018','1/09/2018','1/09/2018',1,8,8,0),
    (1,'NORTE',2018,'3/09/2018','3/09/2018','3/09/2018',1,8,8,0),
    (1,'NORTE',2018,'3/10/2018','3/10/2018','3/10/2018',1,8,8,0),
    (1,'NORTE',2018,'4/10/2018','4/10/2018','4/10/2018',1,8,8,0),
    (1,'NORTE',2018,'5/10/2018','5/10/2018','5/10/2018',1,8,8,0),
    (1,'NORTE',2018,'6/10/2018','6/10/2018','6/10/2018',1,8,8,0),
    (1,'NORTE',2018,'9/10/2018','9/10/2018','9/10/2018',1,8,8,0),
    (1,'NORTE',2018,'10/10/2018','10/10/2018','10/10/2018',1,8,8,0),
    (1,'NORTE',2018,'11/10/2018','11/10/2018','11/10/2018',1,8,8,0),
    (1,'NORTE',2018,'12/10/2018','12/10/2018','12/10/2018',1,8,8,0),
    (1,'NORTE',2018,'13/10/2018','13/10/2018','13/10/2018',1,8,8,0),
    (3,'SUR',2018,'31/10/2018','31/10/2018','31/10/2018',1,8,8,40),
    (1,'NORTE',2018,'1/11/2018','1/11/2018','1/11/2018',1,8,8,0),
    (1,'NORTE',2018,'2/11/2018','2/11/2018','2/11/2018',1,8,8,0);

    WITH cte
         AS (
         SELECT 
                MONTH(t.ultima_fecha) AS mes,
                t.cod_empresa,
                t.razon_social,
                t.anno,
                t.fecha_Labor,
                t.primera_labor,
                t.ultima_Fecha,
                t.dias_trabajados,
                t.total_horas,
                t.promedio_horas,
                t.importe_Total
         FROM @t t)
         SELECT c.cod_empresa,
                MIN(c.fecha_labor) AS FechaLaborMin,
                MAX(c.fecha_labor) AS FechaLaborMax,
                SUM(sum(c.total_horas)) OVER(PARTITION BY c.anno,
                                                     c.mes,
                                                     c.cod_empresa) AS TH
         FROM cte c
         GROUP BY C.cod_empresa,
                  C.anno,
                  C.MES,
                  C.total_horas;



    lunes, 5 de noviembre de 2018 15:07
  • Cual version de SQL Server usas?

    Desde la version 2012 contamos con funciones de off-set (LAG/LEAD) que pueden ser de ayuda en este caso.  El problema se conoce como hayando islas (finding islands).

    La idea es ordenar la data por fecha_labor y comparar el codigo de la empresa con el de la fila anterior para saber cada vez que hay cambio de empresa en el historial (1 si hay cambio / 0 si no hay).  El valor acumulado corriente de este servira para encontrar grupos de filas.

    DECLARE @t TABLE
    (cod_empresa     INT,
     razon_social    VARCHAR(20),
     anno            INT,
     fecha_Labor     DATE,
     primera_labor   DATE,
     ultima_Fecha    DATE,
     dias_trabajados INT,
     total_horas     INT,
     promedio_horas  INT,
     importe_Total   INT
    );
    INSERT INTO @t
    (cod_empresa,
     razon_social,
     anno,
     fecha_Labor,
     primera_labor,
     ultima_Fecha,
     dias_trabajados,
     total_horas,
     promedio_horas,
     importe_Total
    )
    VALUES
    (1,'NORTE',2018,'1/09/2018','1/09/2018','1/09/2018',1,8,8,0),
    (1,'NORTE',2018,'3/09/2018','3/09/2018','3/09/2018',1,8,8,0),
    (1,'NORTE',2018,'3/10/2018','3/10/2018','3/10/2018',1,8,8,0),
    (1,'NORTE',2018,'4/10/2018','4/10/2018','4/10/2018',1,8,8,0),
    (1,'NORTE',2018,'5/10/2018','5/10/2018','5/10/2018',1,8,8,0),
    (1,'NORTE',2018,'6/10/2018','6/10/2018','6/10/2018',1,8,8,0),
    (1,'NORTE',2018,'9/10/2018','9/10/2018','9/10/2018',1,8,8,0),
    (1,'NORTE',2018,'10/10/2018','10/10/2018','10/10/2018',1,8,8,0),
    (1,'NORTE',2018,'11/10/2018','11/10/2018','11/10/2018',1,8,8,0),
    (1,'NORTE',2018,'12/10/2018','12/10/2018','12/10/2018',1,8,8,0),
    (1,'NORTE',2018,'13/10/2018','13/10/2018','13/10/2018',1,8,8,0),
    (3,'SUR',2018,'31/10/2018','31/10/2018','31/10/2018',1,8,8,40),
    (1,'NORTE',2018,'1/11/2018','1/11/2018','1/11/2018',1,8,8,0),
    (1,'NORTE',2018,'2/11/2018','2/11/2018','2/11/2018',1,8,8,0);
    
    WITH R1 AS (
    SELECT
    	*,
    	CASE 
    	WHEN LAG(cod_empresa) OVER(ORDER BY fecha_Labor) <> cod_empresa THEN 1 ELSE 0 END AS grp_helper
    FROM
    	@t
    )
    , R2 AS (
    SELECT
    	*,
    	SUM(R1.grp_helper) OVER(
    	ORDER BY fecha_Labor
    	ROWS UNBOUNDED PRECEDING
    	) AS grp
    FROM
    	R1
    )
    SELECT
    	cod_empresa,
    	MIN(fecha_Labor) AS fecha_labor_min,
    	MAX(fecha_Labor) AS fecha_labor_max
    FROM
    	R2
    GROUP BY
    	cod_empresa,
    	grp
    ORDER BY
    	fecha_labor_min;
    GO

    Puedes leer mas al respecto en el ultimo libro de Itzik Ben-Gan sobre T-SQL Querying.


    AMB

    Some guidelines for posting questions...

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

    lunes, 5 de noviembre de 2018 15:38
  • Hola que tal, estoy usando sql 2014 standar
    lunes, 5 de noviembre de 2018 15:59
  • ok gracias. Lo probe esta funcional

    Saludos


    lunes, 5 de noviembre de 2018 19:13
  • Si te fue de ayuda entonces marcala como respuesta para que otros puedan beneficiarse de esta.


    AMB

    Some guidelines for posting questions...

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

    lunes, 5 de noviembre de 2018 21:25