MySQL - How to combine the column data into one row

Question

I am making an employee attendance record and I have trouble merging the raw data into time in and time out combination format.

From the given "Raw Data" Table below I need to combine the time in and time out of the employee into one row like the "Merge Time in/out" sample below.

Also consider that the employee has two shifting schedule the day shift and night shift. Take note that if the employee is in night shift schedule the time out date is different to time in date.

Day shift empid(ID001,ID002) Night shift empid(ID003)

Raw Data Table
--------------------------------------------
empid      date      time[in/out] in_out
--------------------------------------------
ID001     2014-08-01   7:00am       IN
ID002     2014-08-01   7:01am       IN
ID003     2014-08-01   8:05pm       IN   <--Night Shift

ID001     2014-08-01   5:00pm       OUT
ID002     2014-08-01   5:01pm       OUT
ID003     2014-08-02   6:01am       OUT  <--take note of date


Merge Time in/out Table
--------------------------------------------
empid      date       time_in       time_out
--------------------------------------------
ID001     2014-08-01   7:00am       5:00pm
ID002     2014-08-01   7:01am       5:01pm
ID003     2014-08-01   8:05pm       6:01am

Answer 1

select r1.empid, 
       r1.date, 
       r1.time as time_in, 
       r2.time as time_out
from raw_Data r1
inner join raw_data r2   on r1.empid = r2.empid 
where r1.in_out = 'IN' 
  and r2.in_out = 'OUT';

Answer 2

Ok, so you can tell if the employee worked the night shift when his time_out was AM. In this case, it's the last row's case.

What I did was determinate a real date field. It is the day before when you're out from the night shift, and the current date in any other case

select empid, 
       IF(RIGHT(timeinout,2)='am' AND in_out='OUT',
          DATE_ADD(date, INTERVAL -1 DAY),
          date) as realdate,
       MAX(if(in_out='IN',timeinout,null)) as time_in, 
       MAX(if(in_out='OUT',timeinout,null)) as time_out 
from shifts 
group by empid, realdate

Outputs

depending on the table size it might be worth using this way just for saving yourself a join. In almost any other case, a join is cleaner.

I guess you have no control over the format of the input, so you'll have to stick to times as text and make a comparison for the am/pm suffix in the last 2 characters. I find that rather error prone, but let's pray the raw data will stick to that format.

This solution makes a few assumptions that I rather explain here to avoid further misunderstandings

• The workers can't work the night shift if they worked the day shift (since we're grouping by date, you would need an extra field to distinguish day shift and night shift for a given day)
• Input will never list an OUT time earlier than a IN time for a given day/employee tuple (if it happens, this would need an extra verification step to guarantee consistent output)
• Input will always include timein and timeout for a given shift (if it didn't, you would need an extra step to discard orfan timeentries).

Answer 3

Try this query and tell me if it works

SELECT empid, 
       date, 
       MAX(CASE WHEN in_out = 'IN' THEN time ELSE '' END) time_in, 
       MAX(CASE WHEN in_out = 'OUT' THEN time ELSE '' END) time_out
FROM  Raw Data
GROUP BY empid, date