CODEWITHSUNDEEP
mysqlsql
Pablo
Pablo

Reputation: 1435

Combine Time in and Time out in one row mysql

Currently, I have this table: date_time_records where all of the time in and out of employees are being saved.

enter image description here

As you can see all of the data of time in and out are being stored for each row.

and these data can be identified if it is time in or out using state field

Time in = C/In

Time Out = C/Out

Expected Output

Now I'm trying to do a query something like this

enter image description here

Where you can see the same employee record but different day

and you can notice that the time in and out is being arranged.

Here's my code

 SELECT
      a.id,
      a.ACNo,
      a.name,
      a.email,
       (SELECT MAX(datetime) FROM date_time_records WHERE id = a.id AND state = "C/In") as time_in,
       (SELECT MIN(datetime) FROM date_time_records WHERE id = a.id AND state = "C/Out") as time_out,
  FROM `date_time_records` as a GROUP BY datetime ORDER BY `created_at`  ASC

Please disregard the created_at

I'm using datetime since the system is capable to do a bulk upload of time in and out. of past data.

Upvotes: 0

Views: 863

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270623

Use conditional aggregation:

SELECT dtr.id, dtr.ACNo, dtr.name, dtr.email,
       MAX(CASE WHEN dtr.stat = 'C/IN' THEN dtr.datetime END) as time_in,
       MIN(CASE WHEN dtr.stat = 'C/OUT' THEN dtr.datetime END) as time_out
FROM date_time_records dtr
GROUP BY dtr.id, dtr.ACNo, dtr.name, dtr.email
ORDER BY MIN(created_at) ASC

Upvotes: 1

Matt C
Matt C

Reputation: 1

Could you just log a user's time in/time out separately from the date? That'd make the min/max query for time in/time out on a daily basis a lot simpler.

Upvotes: -1

FanoFN
FanoFN

Reputation: 7124

You could do something like this:

SELECT ACNo, Name,
       SUBSTRING_INDEX(GROUP_CONCAT(CASE WHEN state='C/In' THEN DATETIME END ORDER BY DATETIME ASC),',',1) AS time_in,
       SUBSTRING_INDEX(GROUP_CONCAT(CASE WHEN state='C/Out' THEN DATETIME END ORDER BY DATETIME ASC),',',-1) AS time_out,
       DATE(DATETIME) AS recDate
FROM   date_time_records
GROUP BY ACNo, Name,recDate
ORDER BY ACNo;

Using GROUP_CONCAT then SUBSTRING_INDEX to get the first & last value.

Fiddle here: https://www.db-fiddle.com/f/bfkoKK13kcE8NYVzo71Zi3/3

Upvotes: 2

Related Questions