(edited - I oversimplified the original problem)
I'm trying to figure out a MySQL query that will collapse one-day long contiguous start/end date ranges. Here is my sample input data:
INSERT INTO my_table(tag, start_date, end_date) VALUES ('AA', '2020-07-01', '2020-07-02');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('XB', '2020-07-02', '2020-07-03');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('YC', '2020-07-03', '2020-07-04');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('ZD', '2020-07-04', '2020-07-05');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('0E', '2020-07-05', '2020-07-06');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('CA', '2020-07-06', '2020-07-15');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('CA', '2020-07-20', '2020-07-21');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('CA', '2020-07-21', '2020-07-22');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('CA', '2020-07-22', '2020-07-23');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('EA', '2020-07-23', '2020-07-31');
Here is the output I want:
AA,2020-07-01,2020-07-06
CA,2020-07-06,2020-07-15
CA,2020-07-20,2020-07-23
EA,2020-07-23,2020-07-31
It seems like it should be simple, but I can't figure out how to do it in pure MySQL. I've tried joining the table to itself and using various combinations of MIN() and MAX(), but the solution eludes me. I can do it in PHP code, but would prefer to let MySQL do the work.
I can't rely on the tag
field for grouping (it can be anything). The date collapse should be done only on the date ranges that are 1-day long and contiguous. Date ranges longer than one day should not be collapsed. The selected tag
should be the value associated with the first date in the range
SELECT tag, MIN(start_date) start_date, MAX(end_date) end_date FROM my_table GROUP BY tag;
.SELECT ANY_VALUE(tag) tag, MIN(start_date) start_date, MAX(end_date) end_date FROM my_table GROUP BY LEFT(tag, 1);
. If you need thetag
value taken from the most ancient row strictly then specify your MySQL version.