0
Problem: The user makes several logins and various logouts, wanted to put the login and logout on the same line next to the user name, with the login date and the logout date, on the same line. All records in one line.
2 answers
0
I would use log manipulation for this type of task I think sql would not be the best way to know when the user entered or left the session.
recommend you take a look at file handling . txt
or change the database table as follows
| id | user | ultimo_login | ultimo_logout |
CREATE TABLE table table name(
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
user VARCHAR(60),
ultimo_login VARCHAR(30),
ultimo_logout VARCHAR(30)
);
I put the table dates as string because I don’t know what language you will work with.
0
You can do this query using a subquery.
See working: Sqlfiddle
SELECT
s1.nomusuario [Usuario]
, 'Login/Logout'
, s1.data [Login]
, (
SELECT MIN(data)
FROM sistema [s2]
WHERE s2.nomusuario = s1.nomusuario
AND s2.operacao = 'logout'
AND s2.data > s1.data
)
FROM sistema [s1]
WHERE s1.operacao = 'login'
-
this solution only meets the scenario where there is only one operation record (login/logout) for each user.
Browser other questions tagged sql
You are not signed in. Login or sign up in order to post.
table - system
– ariane
And what bank too...
– Zulian
Put the table structure, are only these 3 columns? has the user ID?
– Caique Romero
How to leave in the same row? in the application? in the database is already structured in columns
– WMomesso
I believe you should consult the login operations and concatenate with the nearest logout operation. But you still have problems if there are no log-in and logout operations registered consistently. Ex.: The user logs in from A and then B, logs out at B and then on A, how will you know which end operation is related to the start one? You still need a column indicating the session of operations, and when there is no logout the information would be blank.
– Leandro Angelo