CODEWITHSUNDEEP
phpmysqldatetime
Luka
Luka

Reputation: 471

MySQL, group by two different columns

My table looks like:

╔═════╦═════════════════════╦═════════════════════╦═════════════╗
║ id  ║        begin        ║         end         ║ employeesId ║
╠═════╬═════════════════════╬═════════════════════╬═════════════╣
║   4 ║ 2015-11-11 00:00:00 ║ 2015-11-11 09:00:00 ║           8 ║
║  80 ║ 2015-11-11 09:00:00 ║ 2015-11-11 12:00:00 ║           8 ║
║  49 ║ 2015-11-11 00:00:00 ║ 2015-11-11 08:00:00 ║          61 ║
║  32 ║ 2015-11-11 08:00:00 ║ 2015-11-12 06:00:00 ║          61 ║
║  42 ║ 2015-11-12 07:00:00 ║ 2015-11-12 13:00:00 ║          61 ║
║  17 ║ 2015-11-11 00:00:00 ║ 2015-11-11 08:00:00 ║          22 ║
║  42 ║ 2015-11-11 09:00:00 ║ 2015-11-11 12:00:00 ║          22 ║
╚═════╩═════════════════════╩═════════════════════╩═════════════╝

I want to merge rows where columns employeesId are equal, and begin time of one row is equal to end time of another row. Like this:

╔═════════════════════╦═════════════════════╦═════════════╗
║        begin        ║         end         ║ employeesId ║
╠═════════════════════╬═════════════════════╬═════════════╣
║ 2015-11-11 00:00:00 ║ 2015-11-11 12:00:00 ║           8 ║
║ 2015-11-11 00:00:00 ║ 2015-11-12 06:00:00 ║          61 ║
║ 2015-11-12 07:00:00 ║ 2015-11-12 13:00:00 ║          61 ║
║ 2015-11-11 00:00:00 ║ 2015-11-11 08:00:00 ║          22 ║
║ 2015-11-11 09:00:00 ║ 2015-11-11 12:00:00 ║          22 ║
╚═════════════════════╩═════════════════════╩═════════════╝

edit:

I need to merge continuous datetimes, in this example you can see that end time '2015-11-11 12:00:00' for employeesId = 8 replaced end time '2015-11-11 09:00:00' in first row because begin of the second row is equal to end of the first row.

Upvotes: 3

Views: 106

Answers (2)

ventaquil
ventaquil

Reputation: 2848

Try join

SELECT a.`begin_date`, IF(b.`end_date` IS NOT NULL, b.`end_date`, a.`end_date`), a.`employer_id`
FROM `mytable` a
LEFT JOIN `mytable` b ON a.`employer_id` = b.`employer_id` AND a.`end_date` = b.`begin_date`
LEFT JOIN `mytable` c ON a.`employer_id` = c.`employer_id` AND a.`begin_date` = c.`end_date`
WHERE c.`id` IS NULL

But you should rebuild your schema.

Upvotes: 2

Subin Chalil
Subin Chalil

Reputation: 3660

You can use MIN(),MAX() if you want to get earlier and later times.

SELECT MIN(begin),MAX(end),employeesId
    FROM table_name
    GROUP BY employeesId

Hope this helps

Upvotes: 0

Related Questions