none
SQL SERVER: Registro de entrada Usuarios RRS feed

  • Pregunta

  • Básicamente, tenemos una aplicación de registro de "empleados". Para simplificar, se registra Login y Logout, y la hora.
    Utilizamos un tabla Temporal:
    SELECT
          [AgentId] Usuario
        , CONVERT(DATE, [ActionLocalTime]) Fecha
        , FORMAT([ActionLocalTime],'HH:mm') HoraMinutos
        ,(SELECT TOP 1 [Description] FROM [dbo].[States] ST WHERE st.Context=Context and st.State=State and st.Detail=StateDetail) StateDescription
        , ActionLocalTime FechaHora
      INTO #ODActionsTable
      FROM [HN_Ondata].[dbo].[ODActions]
      where customerid = 2  AND [AgentId] IN ( '1613', '1614' )
      AND [ActionLocalTimeString] >= '20190613' and [ActionLocalTimeString] <= '20190618'
       AND [Context] = 0  and [State] = 0 and ([StateDetail] = 0 or [StateDetail] = -1) -- LOGIN O LOGOUT
      ORDER BY [AgentId], [ActionLocalTimeString]

      select * from #ODActionsTable order by usuario, FechaHora
    +---------+------------+--------------+--------+-------------------------+
    | Usuario |   Fecha    |         HHMM | State  |        FechaHora        |
    +---------+------------+--------------+--------+-------------------------+
    |    1613 | 2019-06-13 | 08:53        | Login  | 2019-06-13 08:53:06.607 |
    |    1613 | 2019-06-13 | 14:00        | Logout | 2019-06-13 14:00:16.300 |
    |    1613 | 2019-06-17 | 08:50        | Login  | 2019-06-17 08:50:31.013 |
    |    1613 | 2019-06-17 | 14:00        | Logout | 2019-06-17 14:00:12.190 |
    |    1614 | 2019-06-13 | 08:53        | Login  | 2019-06-13 08:53:43.950 |
    |    1614 | 2019-06-13 | 09:06        | Logout | 2019-06-13 09:06:13.013 |
    |    1614 | 2019-06-13 | 09:06        | Login  | 2019-06-13 09:06:23.780 |
    |    1614 | 2019-06-13 | 11:03        | Logout | 2019-06-13 11:03:44.420 |
    |    1614 | 2019-06-13 | 11:04        | Login  | 2019-06-13 11:04:07.277 |
    |    1614 | 2019-06-13 | 11:53        | Logout | 2019-06-13 11:53:19.613 |
    |    1614 | 2019-06-13 | 11:53        | Login  | 2019-06-13 11:53:29.470 |
    |    1614 | 2019-06-13 | 12:37        | Logout | 2019-06-13 12:37:43.180 |
    |    1614 | 2019-06-13 | 12:37        | Login  | 2019-06-13 12:37:52.600 |
    |    1614 | 2019-06-13 | 14:01        | Logout | 2019-06-13 14:01:51.017 |
    |    1614 | 2019-06-17 | 08:49        | Login  | 2019-06-17 08:49:58.810 |
    |    1614 | 2019-06-17 | 10:48        | Logout | 2019-06-17 10:48:16.057 |
    |    1614 | 2019-06-17 | 10:48        | Login  | 2019-06-17 10:48:30.807 |
    |    1614 | 2019-06-17 | 12:36        | Logout | 2019-06-17 12:36:26.897 |
    |    1614 | 2019-06-17 | 12:36        | Login  | 2019-06-17 12:36:38.117 |
    |    1614 | 2019-06-17 | 14:00        | Logout | 2019-06-17 14:00:31.250 |
    +---------+------------+--------------+--------+-------------------------+
    


    El caso más simple, para un día, se hace 1 Login y 1 Logout.
    Un caso más complejo, para un día, se hacen varios Logins y Logouts.
    Consideramos que el primer registro del día será un Login y el último será un Logout.


    viernes, 21 de junio de 2019 11:15

Todas las respuestas


  • Queremos obtener algo así:

    +---------+------------+-------------------------+-------------------------+--------+----+------+-----+-----+---------+---------+---------+
    | usuario |   Fecha    |          Login          |         Logout         
     |   D    | DW | Anyo | Mes | Dia | DiaAnyo | Domingo | Festivo |
    +---------+------------+-------------------------+-------------------------+--------+----+------+-----+-----+---------+---------+---------+
    |    1613 | 2019-06-13 | 2019-06-13 08:53:06.607 | 2019-06-13 14:00:16.300 | Jueves |  4 | 2019 |   6 |  13 |     164 |       0 |      
     0 |
    |    1613 | 2019-06-17 | 2019-06-17 08:50:31.013 | 2019-06-17 14:00:12.190 | Lunes  |  1 | 2019 |   6 |  17 |     168 |       0 |       0
     |
    |    1614 | 2019-06-13 | 2019-06-13 08:53:43.950 | 2019-06-13 14:01:51.017 | Jueves |  4 | 2019 |   6 |  13 |     164 |       0 |      
     0 |
    |    1614 | 2019-06-17 | 2019-06-17 08:49:58.810 | 2019-06-17 14:00:31.250 | Lunes  |  1 | 2019 |   6 |  17 |     168 |       0 |       0
     |
    +---------+------------+-------------------------+-------------------------+--------+----+------+-----+-----+---------+---------+---------+



      SELECT usuario, Fecha 
        , MIN(CASE WHEN StateDescription = 'Login' THEN FechaHora END) AS Login      
        , MAX(CASE WHEN StateDescription = 'Logout' THEN FechaHora  END) AS Logout
        , DATENAME(dw,Fecha) D --DiaSemana
        , DATEPART(dw,Fecha) DW --DiaSemanaCodigo
        , DATEPART(year, Fecha)  Anyo
        , DATEPART(month, Fecha)  Mes
        , DATEPART(day, Fecha)   Dia
        , DATEPART(dayofyear, Fecha)  DiaAnyo
        , CASE WHEN DATEPART(dw,Fecha) = 7   THEN 1 ELSE 0 END Domingo --EsDomingo
        , 0 Festivo --Calcular??
     
    from #ODActionsTable
    GROUP BY  usuario, Fecha 
    order by usuario, Fecha 

    La idea es saber para cada día si ha tenido más de 1 Login- 1 Logout.

    Sugerencias?

    viernes, 21 de junio de 2019 11:15
  • Hola Miriam Pasión:

    Puedes hacer algo similar a esto.

    Lo primero creas una función que te devuelve la siguiente salida/entrada para un user

    CREATE FUNCTION dbo.next_sign
    (@fechahora DATETIME2, 
     @usuario   BIGINT, 
     @status    VARCHAR(10) = 'login'
    )
    RETURNS TABLE
         RETURN
    (
        SELECT TOP (1) odActions.Usuario, 
                       odActions.[State] AS Tipo, 
                       odActions.FechaHora
        FROM odActions
        WHERE usuario = @usuario
              AND fechaHora > @fechahora
              AND state <> @status
        ORDER BY fechahora
    );


    Por defecto siempre la usarás para obtener el logout, pero como el coste es el mismo, pues por si acaso.

    Luego puedes aplicar una consulta de este estilo.

    CREATE TABLE odActions
    (Usuario   BIGINT, 
     Fecha     DATE, 
     HHMM      TIME, 
     State     VARCHAR(10), 
     FechaHora DATETIME2
    );
    GO
    
    insert into odActions (Usuario, fecha, hhmm, State, FechaHora)
    values
    (   1613 ,'2019-06-13','08:53','Login ','2019-06-13 08:53:06.607'),
    (   1613 ,'2019-06-13','14:00','Logout','2019-06-13 14:00:16.300'),
    (   1613 ,'2019-06-17','08:50','Login ','2019-06-17 08:50:31.013'),
    (   1613 ,'2019-06-17','14:00','Logout','2019-06-17 14:00:12.190'),
    (   1614 ,'2019-06-13','08:53','Login ','2019-06-13 08:53:43.950'),
    (   1614 ,'2019-06-13','09:06','Logout','2019-06-13 09:06:13.013'),
    (   1614 ,'2019-06-13','09:06','Login ','2019-06-13 09:06:23.780'),
    (   1614 ,'2019-06-13','11:03','Logout','2019-06-13 11:03:44.420'),
    (   1614 ,'2019-06-13','11:04','Login ','2019-06-13 11:04:07.277'),
    (   1614 ,'2019-06-13','11:53','Logout','2019-06-13 11:53:19.613'),
    (   1614 ,'2019-06-13','11:53','Login ','2019-06-13 11:53:29.470'),
    (   1614 ,'2019-06-13','12:37','Logout','2019-06-13 12:37:43.180'),
    (   1614 ,'2019-06-13','12:37','Login ','2019-06-13 12:37:52.600'),
    (   1614 ,'2019-06-13','14:01','Logout','2019-06-13 14:01:51.017'),
    (   1614 ,'2019-06-17','08:49','Login ','2019-06-17 08:49:58.810'),
    (   1614 ,'2019-06-17','10:48','Logout','2019-06-17 10:48:16.057'),
    (   1614 ,'2019-06-17','10:48','Login ','2019-06-17 10:48:30.807'),
    (   1614 ,'2019-06-17','12:36','Logout','2019-06-17 12:36:26.897'),
    (   1614 ,'2019-06-17','12:36','Login ','2019-06-17 12:36:38.117'),
    (   1614 ,'2019-06-17','14:00','Logout','2019-06-17 14:00:31.250');

    Con tu escenario definido.

    set language Spanish
    ;WITH c
         AS (SELECT o.USUARIO, 
                    O.State AS STATUS, 
                    O.FechaHora AS LOGIN, 
                    U.FECHAHORA AS LOGOUT, 
                    u.tipo
             FROM odActions o
                  CROSS APPLY dbo.next_sign(o.FechaHora, o.Usuario, 'Login') AS u
             WHERE state = 'Login')
         SELECT *, 
                datename(dw, c.Login) AS d,
    		  datepart(dw, c.login)as dw, 
                YEAR(c.Login) as Anyo, 
                MONTH(c.Login) as Mes, 
                DAY(c.login) as Dia, 
    
                DATEPART(dy, c.login) AS DianAnyo,
                CASE
                    WHEN DATEPART(dw, c.login) = 7
                    THEN 1
                    ELSE 0
                END AS Domingo
         FROM c;
    

    Muy importante, tanto el lenguaje de la conexión, a la hora de obtener el mes, como a la hora de insertar los datos.

    También puedes o quizá debes prefijar el cual es el primer día de la semana.

    set language Spanish;
    set datefirst 1;
    ;WITH c
         AS (SELECT o.USUARIO, 
                    O.State AS STATUS, 
                    O.FechaHora AS LOGIN, 
                    U.FECHAHORA AS LOGOUT, 
                    u.tipo
             FROM odActions o
                  CROSS APPLY dbo.next_sign(o.FechaHora, o.Usuario, 'Login') AS u
             WHERE state = 'Login')
         SELECT *, 
                datename(dw, c.Login) AS d,
    		  datepart(dw, c.login)as dw, 
                YEAR(c.Login) as Anyo, 
                MONTH(c.Login) as Mes, 
                DAY(c.login) as Dia, 
    
                DATEPART(dy, c.login) AS DianAnyo,
                CASE
                    WHEN DATEPART(dw, c.login) = 7
                    THEN 1
                    ELSE 0
                END AS Domingo
         FROM c;
    

    A efectos de variar ese when datepart(dw, c.login) = 7

    Una variante para saber si ha realizado más de un login al día puede ser así

    	set language Spanish;
    set datefirst 1;
    ;WITH c
         AS (SELECT o.USUARIO, 
                    O.State AS STATUS, 
                    O.FechaHora AS LOGIN, 
                    U.FECHAHORA AS LOGOUT, 
                    u.tipo
             FROM odActions o
                  CROSS APPLY dbo.next_sign(o.FechaHora, o.Usuario, 'Login') AS u
             WHERE state = 'Login')
         SELECT *, 
                datename(dw, c.Login) AS d,
    		  datepart(dw, c.login)as dw, 
                YEAR(c.Login) as Anyo, 
                MONTH(c.Login) as Mes, 
                DAY(c.login) as Dia, 
    
                DATEPART(dy, c.login) AS DianAnyo,
    		  row_number() over (partition by c.usuario, cast(c.login as date) order by c.usuario, c.login) as row,
                CASE
                    WHEN DATEPART(dw, c.login) = 7
                    THEN 1
                    ELSE 0
                END AS Domingo
         FROM c;

    Estableciendo una numeración de fila por cada usuario y fecha del año de manera que si repite login, tendrás en la columna row un numero mayor que 1.

    Salida

    Row_number

    https://javifer2.blogspot.com/2019/01/numerar-filas-funciones-de-ventana.html

    Tabla de expresión común

    https://javifer2.blogspot.com/search/label/tablas%20de%20expresi%C3%B3n%20com%C3%BAn%20%281%29

    viernes, 21 de junio de 2019 17:58
  • Otra variante que no has tenido en cuenta pero que quizá sea importante, es que haya login pero no logout.

    Mismo escenario pero con menos registros

    delete from odActions
    insert into odActions (Usuario, fecha, hhmm, State, FechaHora)
    values
    (   1613 ,'2019-06-13','08:53','Login ','2019-06-13 08:53:06.607'),
    (   1613 ,'2019-06-13','14:00','Logout','2019-06-13 14:00:16.300'),
    (   1613 ,'2019-06-17','08:50','Login ','2019-06-17 08:50:31.013'),
    
    (   1614 ,'2019-06-17','12:36','Login ','2019-06-17 12:36:38.117'),
    (   1614 ,'2019-06-17','14:00','Logout','2019-06-17 14:00:31.250');

    El 1613 entra el dia 17, pero no ficha al salir...

    	set language Spanish;
    set datefirst 1;
    ;WITH c
         AS (SELECT o.USUARIO, 
                    O.State AS STATUS, 
                    O.FechaHora AS LOGIN, 
                    U.FECHAHORA AS LOGOUT, 
                    u.tipo
             FROM odActions o
    
    /* en vez de cross apply utilizamos outer apply */
                  outer APPLY dbo.next_sign(o.FechaHora, o.Usuario, 'Login') AS u
             WHERE state = 'Login')
         SELECT *, 
                datename(dw, c.Login) AS d,
    		  datepart(dw, c.login)as dw, 
                YEAR(c.Login) as Anyo, 
                MONTH(c.Login) as Mes, 
                DAY(c.login) as Dia, 
    
                DATEPART(dy, c.login) AS DianAnyo,
    		  row_number() over (partition by c.usuario, cast(c.login as date) order by c.usuario, c.login) as row,
                CASE
                    WHEN DATEPART(dw, c.login) = 7
                    THEN 1
                    ELSE 0
                END AS Domingo
         FROM c;
    

    Salida

    viernes, 21 de junio de 2019 18:02
  • Deleted
    sábado, 22 de junio de 2019 12:48
  • Muchas gracias por las respuestas.
    ---
    Buen apunte, sin duda, importante el  lenguaje de la conexión, y el primer día de la semana.
    ---
    Utilizando ROW (por usuario y fecha del año) sabré si hay más 1 login-logout, y finalmente tendré que quedarme con el Login del ROW = 1 y el Logout del ROW = 5, el maxímo de ROW por cada usuario y fecha del año.
    ---
    Sí, podría darse que haya login pero no logout.
    ---
    SQL Server 2016, y también tendremos que hacer consultas a SQL Server 2012.
    Ejemplos de las funciones LAG() y LEAD().
    ---
    Gran idea, sin duda, como comentas el problema se ajusta al caso clásico de "packing intervals". Recomiendan rerferencias o varias implementaciones listas?

    Igual hay que hacer otra pregunta en el foro, pero el siguiente paso es que el Login puede hacerse un día (23:00 horas por ejemplo) y el Logout al día siguiente (7:00 am), cuando se trabaja de noche. Todavía no sé cómo plantearlo, pero creo que puede encajar con "packing intervals".
    lunes, 24 de junio de 2019 15:02