none
Obtener subtotales - acumulados de registros de usuarios RRS feed

  • Pregunta

  • Uso SQL Server 2016 Standard, aunque también obtengo datos de SQL SERVER 2012.

    Utilizo tablas temporales, y al final logro esta información:

    #HistoricoUserTable2
    usuario      | UsuarioHermes |   Fecha    |       FechaLogin        |       FechaLogout       | DiaSemana

    #ODActionsTable2
    usuarioHermes |   Fecha    |       FechaLogin2       |      FechaLogout2       | DiaSemana |


    Nota: La diferencia entre FechaLogin y FechaLogin2, y  FechaLogout y  FechaLogout2 es mínima, pocos segundos, a priori.

    select * from #HistoricoUserTable2  H2 FULL OUTER JOIN #ODActionsTable2 A2 ON H2.UsuarioHermes = A2.UsuarioHermes AND H2.Fecha = A2.Fecha ORDER BY H2.UsuarioHermes, H2.FECHA


    Los resultados quedarían así:

    +-------------------+---------------+------------+-------------------------+-------------------------+-----------+---------------+------------+-------------------------+-------------------------+-----------+
    |      usuario      | UsuarioHermes |   Fecha    |       FechaLogin        |       FechaLogout       | DiaSemana | usuarioHermes |   Fecha    |       FechaLogin2       |      FechaLogout2       | DiaSemana |
    +-------------------+---------------+------------+-------------------------+-------------------------+-----------+---------------+------------+-------------------------+-------------------------+-----------+
    | aida.X            |          1613 | 2019-06-13 | 2019-06-13 08:52:04.000 | 2019-06-13 14:00:22.000 | Jueves    | 1613          | 2019-06-13 | 2019-06-13 08:53:06.607 | 2019-06-13 14:00:16.300 | Jueves    |
    | aida.X            |          1613 | 2019-06-17 | 2019-06-17 08:49:35.000 | 2019-06-17 14:00:15.000 | Lunes     | 1613          | 2019-06-17 | 2019-06-17 08:50:31.013 | 2019-06-17 14:00:12.190 | Lunes     |
    | aida.X            |          1613 | 2019-06-18 | 2019-06-18 08:48:39.000 | 2019-06-18 14:00:40.000 | Martes    | NULL          | NULL       | NULL                    | NULL                    | NULL      |
    | alejandra.Z       |          1614 | 2019-06-13 | 2019-06-13 08:40:58.000 | 2019-06-13 14:01:56.000 | Jueves    | 1614          | 2019-06-13 | 2019-06-13 08:53:43.950 | 2019-06-13 14:01:51.017 | Jueves    |
    | alejandra.Z       |          1614 | 2019-06-17 | 2019-06-17 08:36:28.000 | 2019-06-17 14:00:33.000 | Lunes     | 1614          | 2019-06-17 | 2019-06-17 08:49:58.810 | 2019-06-17 14:00:31.250 | Lunes     |
    | alejandra.Z       |          1614 | 2019-06-18 | 2019-06-18 08:50:16.000 | 2019-06-18 14:01:35.000 | Martes    | NULL          | NULL       | NULL                    | NULL                    | NULL      |
    +-------------------+---------------+------------+-------------------------+-------------------------+-----------+---------------+------------+-------------------------+-------------------------+-----------+



    La idea es:

    A partir de la "Fecha", añadir columnas Domingo (si la fecha es Domingo), Festivo (sabemos la Provincia del usuario, para calcular si el día es festivo nacional o autonómico), Nocturno (si las horas son nocturnas).

    Y obtener "subtotales" de cada usuario:

    TOTAL Dias que ha trabajado: 3 Domingos: 0 Festivos: 0 Días que trabajó de noche: 0



    +-------------------+---------------+------------+-------------------------+-------------------------+-----------+---------------+------------+-------------------------+-------------------------+-----------+
    |      usuario      | UsuarioHermes |   Fecha    |       FechaLogin        |       FechaLogout       | DiaSemana | usuarioHermes |   Fecha    |       FechaLogin2       |      FechaLogout2       | DiaSemana |
    +-------------------+---------------+------------+-------------------------+-------------------------+-----------+---------------+------------+-------------------------+-------------------------+-----------+
    | aida.X            |          1613 | 2019-06-13 | 2019-06-13 08:52:04.000 | 2019-06-13 14:00:22.000 | Jueves    | 1613          | 2019-06-13 | 2019-06-13 08:53:06.607 | 2019-06-13 14:00:16.300 | Jueves    |
    | aida.X            |          1613 | 2019-06-17 | 2019-06-17 08:49:35.000 | 2019-06-17 14:00:15.000 | Lunes     | 1613          | 2019-06-17 | 2019-06-17 08:50:31.013 | 2019-06-17 14:00:12.190 | Lunes     |
    | aida.X            |          1613 | 2019-06-18 | 2019-06-18 08:48:39.000 | 2019-06-18 14:00:40.000 | Martes    | NULL          | NULL       | NULL                    | NULL                    | NULL      |
    | TOTAL Dias que ha trabajado: 3     Domingos: 0     Festivos: 0     Días que trabajó de noche: 0
    | alejandra.Z       |          1614 | 2019-06-13 | 2019-06-13 08:40:58.000 | 2019-06-13 14:01:56.000 | Jueves    | 1614          | 2019-06-13 | 2019-06-13 08:53:43.950 | 2019-06-13 14:01:51.017 | Jueves    |
    | alejandra.Z       |          1614 | 2019-06-17 | 2019-06-17 08:36:28.000 | 2019-06-17 14:00:33.000 | Lunes     | 1614          | 2019-06-17 | 2019-06-17 08:49:58.810 | 2019-06-17 14:00:31.250 | Lunes     |
    | alejandra.Z       |          1614 | 2019-06-18 | 2019-06-18 08:50:16.000 | 2019-06-18 14:01:35.000 | Martes    | NULL          | NULL       | NULL                    | NULL                    | NULL      |
    | TOTAL Dias que ha trabajado: 3     Domingos: 0     Festivos: 0     Días que trabajó de noche: 0
    +-------------------+---------------+------------+-------------------------+-------------------------+-----------+---------------+------------+-------------------------+-------------------------+-----------+

    Sería posible hacerlo en SQL SERVER, esos subtotales de cada usuario?

    miércoles, 26 de junio de 2019 10:50

Respuestas

Todas las respuestas

  • Veamos si esto le sirve

    http://www.sqlusa.com/bestpractices2005/subtotaltotalgrandtotal/

    En cuanto a los dias festivos, creo que deberia crear una tabla con estos dias


    IIslas Master Consultant SQL Server

    miércoles, 26 de junio de 2019 18:00
  • Tengo esta consulta finalmente,

    DECLARE @TablaCalendarioNacional dbo.TipoTablaCalendarioNacional;
    INSERT INTO @TablaCalendarioNacional SELECT * FROM #CalendarioNacional;
    
    DECLARE @TablaCalendarioAutonomico dbo.TipoTablaCalendarioAutonomico;
    INSERT INTO @TablaCalendarioAutonomico SELECT * FROM #CalendarioAutonomico;
    
    SELECT 
    
    UsuHermes, FechaDia, HoraLogin, HoraLogout
    , DATENAME(dw, FechaDia) DiaSemana
    , DATEPART(dw,FechaDia) DiaSemanaCodigo
    , DATEPART(year, FechaDia)  Anyo
    , DATEPART(month, FechaDia)  Mes
    , DATEPART(day, FechaDia)   Dia
    , DATEPART(dayofyear, FechaDia)  DiaAnyo
    
    , CASE WHEN DATEPART(dw,FechaDia) = 7   THEN 1 ELSE 0 END EsDomingo
    , [dbo].[ufn_EsFestivo](@TablaCalendarioNacional, @TablaCalendarioAutonomico, FechaDia, UsuHermes) EsFestivo
    
    , (CASE WHEN (HoraLogin > '22:05:00' OR  HoraLogin  < '08:05:00') OR  (HoraLogout > '22:05:00' OR  HoraLogout  < '08:05:00') THEN 1 ELSE 0 END) PlusNocturnidad 
    , (CASE WHEN (HoraLogin > '00:05:00' AND  HoraLogin  < '06:05:00') OR  (HoraLogout > '00:05:00' AND  HoraLogout  < '06:05:00') THEN 1 ELSE 0 END) PlusTransporte
     
    FROM #DatosRegistros 
    --GROUP BY UsuHermes, FechaDia
    ORDER BY UsuHermes, FechaDia
    
    

    Y estos datos de resultado:

    +-----------+------------+------------------+------------------+-----------+-----------------+------+-----+-----+---------+-----------+-----------+-----------------+----------------+
    | UsuHermes |  FechaDia  |    HoraLogin     |    HoraLogout    | DiaSemana | DiaSemanaCodigo | Anyo | Mes | Dia | DiaAnyo | EsDomingo | EsFestivo | PlusNocturnidad | PlusTransporte |
    +-----------+------------+------------------+------------------+-----------+-----------------+------+-----+-----+---------+-----------+-----------+-----------------+----------------+
    |      1613 | 2019-06-13 | 08:53:06.6066667 | 14:00:16.3000000 | Jueves    |               4 | 2019 |   6 |  13 |     164 |         0 |         0 |               0 |              0 |
    |      1613 | 2019-06-17 | 08:50:31.0133333 | 14:00:12.1900000 | Lunes     |               1 | 2019 |   6 |  17 |     168 |         0 |         0 |               0 |              0 |
    |      1613 | 2019-06-18 | 08:48:39.0000000 | 14:00:40.0000000 | Martes    |               2 | 2019 |   6 |  18 |     169 |         0 |         0 |               0 |              0 |
    ====================> QUIERO AÑADIR FILA DE SUBTOTAL 

    | 1614 | 2019-06-13 | 08:53:43.9500000 | 14:01:51.0166667 | Jueves | 4 | 2019 | 6 | 13 | 164 | 0 | 0 | 0 | 0 | | 1614 | 2019-06-17 | 08:49:58.8100000 | 14:00:31.2500000 | Lunes | 1 | 2019 | 6 | 17 | 168 | 0 | 0 | 0 | 0 | | 1614 | 2019-06-18 | 08:50:16.0000000 | 14:01:35.0000000 | Martes | 2 | 2019 | 6 | 18 | 169 | 0 | 0 | 0 | 0 |
    ====================> QUIERO AÑADIR FILA DE SUBTOTAL
    +-----------+------------+------------------+------------------+-----------+-----------------+------+-----+-----+---------+-----------+-----------+-----------------+----------------+

    Quiero añadir subtotales, por usuario, para EsDomingo, EsFestivo, PlusNocturnidad, PlusTransporte.

    Quiero hacer GROUP BY UsuHermes, peor no funciona si no añado SUM, a los otros campos.

    Sería posible hacerlo?

    viernes, 28 de junio de 2019 11:35
  • Deleted
    • Marcado como respuesta Miriam Pasion jueves, 4 de julio de 2019 13:36
    domingo, 30 de junio de 2019 11:07
  • Gracias infinitas. Sin duda con With - CTE da mucho juego, y con el campo Origen.

    Haciendo un ajuste para que aparezca la palabra TOTAL

    SELECT UsuHermes, case when Origen = 0 then FechaDia else  'TOTAL: ' + FechaDia end FechaDia, HoraLogin, HoraLogout,
           DiaSemana, Anyo, Mes, Dia, DiaAnyo, EsDomingo,
           EsFestivo, PlusNocturnidad, PlusTransporte
      from Juntos
      order by UsuHermes, Origen, case when Origen = 0 then FechaDia_order end;

    Este es el resultado:

    +-----------+------------+------------------+------------------+-----------+------+------+------+---------+-----------+-----------+-----------------+----------------+
    | UsuHermes |  FechaDia  |    HoraLogin     |    HoraLogout    | DiaSemana | Anyo | Mes  | Dia  | DiaAnyo | EsDomingo | EsFestivo | PlusNocturnidad | PlusTransporte |
    +-----------+------------+------------------+------------------+-----------+------+------+------+---------+-----------+-----------+-----------------+----------------+
    |      1613 | 13/06/2019 | 08:53:06.6066667 | 14:00:16.3000000 | Jueves    | 2019 | 6    | 13   | 164     |         0 |         0 |               0 |              0 |
    |      1613 | 17/06/2019 | 08:50:31.0133333 | 14:00:12.1900000 | Lunes     | 2019 | 6    | 17   | 168     |         0 |         0 |               0 |              0 |
    |      1613 | 18/06/2019 | 08:48:39.0000000 | 14:00:40.0000000 | Martes    | 2019 | 6    | 18   | 169     |         0 |         0 |               0 |              0 |
    |      1613 | TOTAL: 3   | NULL             | NULL             | NULL      | NULL | NULL | NULL | NULL    |         0 |         0 |               0 |              0 |
    |      1614 | 13/06/2019 | 08:53:43.9500000 | 14:01:51.0166667 | Jueves    | 2019 | 6    | 13   | 164     |         0 |         0 |               0 |              0 |
    |      1614 | 17/06/2019 | 08:49:58.8100000 | 14:00:31.2500000 | Lunes     | 2019 | 6    | 17   | 168     |         0 |         0 |               0 |              0 |
    |      1614 | 18/06/2019 | 08:50:16.0000000 | 14:01:35.0000000 | Martes    | 2019 | 6    | 18   | 169     |         0 |         0 |               0 |              0 |
    |      1614 | TOTAL: 3   | NULL             | NULL             | NULL      | NULL | NULL | NULL | NULL    |         0 |         0 |               0 |              0 |
    +-----------+------------+------------------+------------------+-----------+------+------+------+---------+-----------+-----------+-----------------+----------------+

    Ahora otra duda: crear un Procedimiento almacenado con los parámetros adecuados o no (sino un montón de consultas a pelo).

    Se tiene que invocar desde C# (ASP.NET) con Dapper, o en su defecto, ADO.NET.

    jueves, 4 de julio de 2019 13:35
  • Deleted
    jueves, 4 de julio de 2019 13:50
  • Reviso todas las consultas SQL de prueba que tengo, y podría decir:

    1) El identificador de grupo de  usuarios:

     -- Usuarios del Grupo de Supervisión
     SELECT *  FROM CRM_Inari.dbo.Usuarios_GruposSup c  WHERE IdGrupoSup = 
     ( 
     SELECT  TOP 1 ID from CRM_Inari.dbo.GruposSupervision b  where [CodCamp] = (select top 1 [CodCamp]    FROM [BBDD_Costes].[dbo].[CostesInforme]  where FirstCamp = 'xxxxx')  AND EnUso = 'True'
     )
    

    2) Rango de fechas

      AND [ActionLocalTimeString] >= '20190613' and [ActionLocalTimeString] <= '20190618'

    Además,

    El UNPIVOT de las tablas calendarios ahora son tablas temporales.

    Utilizo valores a pelo, en lugar de constantes, o pasar valores (o lista de valores) por parámetro:

      AND EVENTO IN ('LOGIN', 'LOGOUT')
    
    
    WHERE [Cod_ comunid_ auton_] in ('01','09','12') and  ano in (YEAR(GETDATE()) - 1, YEAR(GETDATE()), YEAR(GETDATE()) + 1) 
    
    
    
     (CASE WHEN (HoraLogin > '22:05:00' OR  HoraLogin  < '08:05:00') 
                      OR  (HoraLogout > '22:05:00' OR  HoraLogout  < '08:05:00') 
                 THEN 1 ELSE 0 END) PlusNocturnidad, 
           (CASE WHEN (HoraLogin > '00:05:00' AND  HoraLogin  < '06:05:00') 
                      OR  (HoraLogout > '00:05:00' AND  HoraLogout  < '06:05:00') 
                 THEN 1 ELSE 0 END) PlusTransporte
    
    
    
     -- todo: Comunidad
       DECLARE @Comunidad NVARCHAR(2)
    	if @usuHermes like '1%'        -- 'MAD','DGT','TA'
    		SET @Comunidad = '12';     -- MADRID
        else if @usuHermes like '3%'   -- SEV
    		SET @Comunidad = '01';     -- ANDALUCIA
    	else if @usuHermes like '4%'   -- BCN
    		SET @Comunidad = '09';     -- CATALUÑA
    	else
    		SET @Comunidad = NULL;
    





    jueves, 4 de julio de 2019 14:49