CODEWITHSUNDEEP
mysql
Drz
Drz

Reputation: 65

Query Mysql for select in 1 column but have 2 different type data

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

Answers (1)

cdaiga
cdaiga

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

Related Questions