This is a sample output of my table(time_entries)
user_name| entry_type | entry_datetime
User1 | Time In | 28-JUL-13 16:40:40
User1 | Time Out | 28-JUL-13 16:40:41
User2 | Time In | 28-JUL-13 16:41:13
User2 | Time Out | 28-JUL-13 16:41:15
User1 | Time In | 27-JUL-13 16:42:30
User1 | Time Out | 27-JUL-13 16:42:34
User2 | Time In | 27-JUL-13 16:43:32
User2 | Time Out | 27-JUL-13 16:43:35
Now I used this query
SELECT te.user_name, te.entry_name, MAX(te.entry_datetime) AS date
FROM time_entries AS te
GROUP BY te.entry_type, te.user_name
and the result is this
user_name| entry_type | entry_datetime
User1 | Time In | 28-JUL-13 16:40:40
User1 | Time Out | 28-JUL-13 16:40:41
User2 | Time In | 28-JUL-13 16:41:13
User2 | Time Out | 28-JUL-13 16:41:15
QUESTION: Is there a way to merge username with the same name in 1 line? and have an output something like this.
user_name| Date | Timein | Timeout
User1 | 28-JUL-13 | 16:40:40 | 16:40:41
User2 | 28-JUL-13 | 16:41:13 | 16:41:15
I'm a little confuse how to do it.
You can concatenate rows into single string using COALESCE method. This COALESCE method can be used in SQL Server version 2008 and higher. All you have to do is, declare a varchar variable and inside the coalesce, concat the variable with comma and the column, then assign the COALESCE to the variable.
I would approach it in this manner:
The end result:
SELECT te.user_name, LEFT(te.entry_datetime, 10) AS entry_date, cin.entry_datetime AS timein, cout.entry_datetime AS timeout
FROM time_entries te
LEFT JOIN (SELECT user_name, LEFT(entry_datetime, 10) AS entry_date, MIN(entry_datetime) AS entry_datetime
FROM time_entries
WHERE entry_type = 'Time In'
GROUP BY user_name, entry_date
) cin ON cin.user_name = te.user_name AND cin.entry_date = LEFT(te.entry_datetime, 9)
LEFT JOIN (SELECT user_name, LEFT(entry_datetime, 10) AS entry_date, MAX(entry_datetime) AS entry_datetime
FROM time_entries
WHERE entry_type = 'Time Out'
GROUP BY user_name, entry_date
) cout ON cout.user_name = te.user_name AND cout.entry_date = LEFT(te.entry_datetime, 10)
GROUP BY te.user_name, entry_date;
Demo
Btw, I'm using LEFT()
here because your column doesn't contain an actual date format that MySQL understands.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With