Time calculation logged in to SQL

Asked

Viewed 165 times

2

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

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 consultation works, but there is the case of employees who work at dawn, and end up having time counted on separate days.

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

sql sql-server logic

  • I didn’t understand the LOGOUT and LOGIN columns, are they date type? They mark the Login date and the Logout date, that’s it?

1 answer

1


A way out is for you to create a CASE WHEN on its internal date for when it is dawn you return day-1. Assuming you want to group until 6 am the next day.

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

Browser other questions tagged sql sql-server logic

You are not signed in. Login or sign up in order to post.