Calculation of time logged in SQL

Navigation
2

I have a system where user logs in and out of the tool several times in the day during their working hours. I need to calculate the amount of time it was logged:

The columns LOGOUT and LOGIN are in seconds. 

SELECT DATA,USUARIO,SUM(PERIODO_LOGADO) AS TOTAL_LOGADO
FROM (
    SELECT DATA,USUARIO,LOGOUT-LOGIN AS PERIODO_LOGADO
    FROM HISTORICO_LOGIN
    GROUP BY DATA,USUARIO,LOGOUT-LOGIN
) SUB
GROUP BY DATA,USUARIO

This query works, but there is the case of employees who work at dawn, and end up having the time accounted for on separate days.

How to merge the two times or calculate the total hours worked dynamically?

    
asked by anonymous 07.08.2015 / 01:20

1 answer

1

One way out is to create a CASE WHEN on your internal date so when it is at dawn you return day-1. Assuming you want to group until 6am the next morning. 

SELECT DATA,USUARIO,SUM(PERIODO_LOGADO) AS TOTAL_LOGADO
FROM (
    SELECT CASE WHEN DATEPART(HOUR, DATA) < 6
                THEN DATEADD(DAY, -1, DATA)
                ELSE DATA 
           END AS DATA,
           USUARIO,LOGOUT-LOGIN AS PERIODO_LOGADO
    FROM HISTORICO_LOGIN
    GROUP BY DATA,USUARIO,LOGOUT-LOGIN
) SUB
GROUP BY DATA,USUARIO
    
07.08.2015 / 01:51
Input within DIV with visibility: hidden Input hidden + select