Reputation: 65
I have 1 table like this
id|Date |Username|TimeLogin|Type
1 |2018-01-22|tester1 |07:00 |Login
2 |2018-01-22|tester1 |22:00 |Logout
3 |2018-01-22|user2 |08:00 |Login
4 |2018-01-22|user2 |18:00 |Logout
I want the result to be like this where Type
being Login
is made into one column and the Logout
entry is made into another column in the result.
username|logintime|logouttime|date
tester1 |07:00 |22:00 |2018-01-22
user2 |08:00 |18:00 |2018-01-22
i try this query but the result it's not correct
SELECT
username,
CASE WHEN type = 'Login' THEN
timeLogin
END AS 'loginTime',
CASE WHEN type = 'Logout' THEN
timeLogin
END AS 'logoutTime',
Date
FROM
sys_transaksi
WHERE
Date = '2018-01-22'
GROUP BY username
Can anyone help me with this?
Upvotes: 0
Views: 29
Reputation: 4937
You can obtain something close to the expected result by doing an inner join as illustrated below:
SELECT A.username, A.timeLogin `loginTime`, B.timeLogin `logoutTime`, A.date
FROM
(SELECT username, `date`, max(timeLogin) timeLogin
FROM sys_transaksi WHERE type='login' AND `date`='2018-01-22'
GROUP BY username, `date`) A
LEFT JOIN
(SELECT username, `date`, max(timeLogin) timeLogin
FROM sys_transaksi WHERE type='logout' AND `date`='2018-01-22'
GROUP BY username, `date`) B
ON A.username=B.username AND A.timeLogin<B.timeLogin;
This gives the most recent login and logout time of each user for a specified date. It will be better to modify the structure of your table, so as to manage the login and logout time of each user session as @FrankerZ proposes in his comments.
Upvotes: 1