Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Merge two rows with a unique attribute

Tags:

sql

php

mysql

max

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.

like image 737
bot Avatar asked Jul 30 '13 06:07

bot


People also ask

How do I combine two row values in SQL?

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.


1 Answers

I would approach it in this manner:

  1. Create a query that returns the "time in" time for each user and date.
  2. Create a query like #1 that returns the "time out" time for each user and date.
  3. Merge the results together using LEFT JOIN.

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.

like image 66
Ja͢ck Avatar answered Oct 14 '22 08:10

Ja͢ck