A query that only works for some cases

Question

A query that only works for some cases

I have a table similar to the following structure:

City        start_date             end_date
Paris       1995-01-01 00:00:00    1997-10-01 23:59:59
Paris       1997-10-02 00:00:00    0001-01-01 00:00:00
Paris       2013-01-25 00:00:00    0001-01-01 00:00:00
Paris       2015-04-25 00:00:00    0001-01-01 00:00:00
Berlin      2014-11-01 00:00:00    0001-01-01 00:00:00
Berlin      2014-06-01 00:00:00    0001-01-01 00:00:00
Berlin      2015-09-11 00:00:00    0001-01-01 00:00:00
Berlin      2015-10-01 00:00:00    0001-01-01 00:00:00
Milan       2001-01-01 00:00:00    0001-01-01 00:00:00
Milan       2005-10-02 00:00:00    2006-10-02 23:59:59
Milan       2006-10-03 00:00:00    2015-04-24 23:59:59
Milan       2015-04-25 00:00:00    0001-01-01 00:00:00

Data contains a historical view of start and end dates based on cities. The most recent entry for the city should be the one with the highest start date and end date “0001-01-01 00:00:00”, which indicates that there is no end date yet.

I need to clear this data and make sure that the historical records for each city have end dates one second before the next recording start date , only in cases when the end_date parameter is set to '0001-01-01 00:00:00'. Therefore in cases where end_date has an actual date, this will be ignored.In addition, the record with the most recent start_date for the city does not need to be changed end_date.

The summary table should look like this:

City        start_date             end_date
Paris       1995-01-01 00:00:00    1997-10-01 23:59:59
Paris       1997-10-02 00:00:00    2013-01-24 23:59:59
Paris       2013-01-25 00:00:00    2015-04-24 23:59:59
Paris       2015-04-25 00:00:00    0001-01-01 00:00:00
Berlin      2014-11-01 00:00:00    2014-05-31 23:59:59
Berlin      2014-06-01 00:00:00    2015-09-10 23:59:59
Berlin      2015-09-11 00:00:00    2015-09-30 23:59:59
Berlin      2015-10-01 00:00:00    0001-01-01 23:59:59
Milan       2001-01-01 00:00:00    2005-10-01 23:59:59
Milan       2005-10-02 00:00:00    2006-10-02 23:59:59
Milan       2006-10-03 00:00:00    2015-04-24 23:59:59
Milan       2015-04-25 00:00:00    0001-01-01 00:00:00

I tried the following script suggested by the user in this question .

update test join
       (select t.*,
               (select min(start_date)
                from test t2
                where t2.city = t.city and
                      t2.start_date > t.start_date
                order by t2.start_date
                limit 1
               ) as next_start_date
        from test t
       ) tt
       on tt.city = test.city and tt.start_date = test.start_date
    set test.end_date = date_sub(tt.next_start_date, interval 1 second)
where test.end_date = '0001-01-01' and
      next_start_date is not null;

Unfortunately, some end_dates are not intended (e.g. id 5 and 6), starting with entries in Berlin. However, others appear, as intended. This is shown below:

The following are the create and insert instructions for replication:

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `city` varchar(50) DEFAULT NULL,
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

INSERT INTO test (city, start_date, end_date) VALUES ('Paris','1995-01-01 00:00:00','1997-10-01 23:59:59');
INSERT INTO test (city, start_date, end_date) VALUES ('Paris','1997-10-02 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Paris','2013-01-25 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Paris','2015-04-25 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Berlin','2014-11-01 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Berlin','2014-06-01 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Berlin','2015-09-11 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Berlin','2015-10-01 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Milan','2001-01-01 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Milan','2005-10-02 00:00:00','2006-10-02 23:59:59');
INSERT INTO test (city, start_date, end_date) VALUES ('Milan','2006-10-03 00:00:00','2015-04-24 23:59:59');
INSERT INTO test (city, start_date, end_date) VALUES ('Milan','2015-04-25 00:00:00','0001-01-01 00:00:00');

+4

date sql database mysql relational-database

Dot NET 21 . '16 8:06

1

Dylan Su · Answer 1 · 2016-03-21T08:42:46+0000

-- query wanted
UPDATE test t1 INNER JOIN
    (SELECT *, @id := @id + 1 AS new_id 
     FROM test CROSS JOIN (SELECT @id := 0) param
     ORDER BY city, start_date) t2
     ON t1.city = t2.city AND t1.start_date = t2.start_date
     INNER JOIN 
    (SELECT *, @id2 := @id2 + 1 AS new_id 
     FROM test CROSS JOIN (SELECT @id2 := 0) param
     ORDER BY city, start_date) t3 
     ON t2.new_id + 1 = t3.new_id AND t2.city = t3.city
SET t1.end_date = DATE_SUB(t3.start_date, INTERVAL 1 SECOND)
WHERE t1.end_date = '0001-01-01 00:00:00';

.

SQL:

-- data
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `city` varchar(50) DEFAULT NULL,
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

INSERT INTO test (city, start_date, end_date) VALUES ('Paris','1995-01-01 00:00:00','1997-10-01 23:59:59');
INSERT INTO test (city, start_date, end_date) VALUES ('Paris','1997-10-02 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Paris','2013-01-25 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Paris','2015-04-25 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Berlin','2014-11-01 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Berlin','2014-06-01 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Berlin','2015-09-11 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Berlin','2015-10-01 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Milan','2001-01-01 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Milan','2005-10-02 00:00:00','2006-10-02 23:59:59');
INSERT INTO test (city, start_date, end_date) VALUES ('Milan','2006-10-03 00:00:00','2015-04-24 23:59:59');
INSERT INTO test (city, start_date, end_date) VALUES ('Milan','2015-04-25 00:00:00','0001-01-01 00:00:00');
select * from test;

-- query wanted
UPDATE test t1 INNER JOIN
    (SELECT *, @id := @id + 1 AS new_id 
     FROM test CROSS JOIN (SELECT @id := 0) param
     ORDER BY city, start_date) t2
     ON t1.city = t2.city AND t1.start_date = t2.start_date
     INNER JOIN 
    (SELECT *, @id2 := @id2 + 1 AS new_id 
     FROM test CROSS JOIN (SELECT @id2 := 0) param
     ORDER BY city, start_date) t3 
     ON t2.new_id + 1 = t3.new_id AND t2.city = t3.city
SET t1.end_date = DATE_SUB(t3.start_date, INTERVAL 1 SECOND)
WHERE t1.end_date = '0001-01-01 00:00:00';

select * from test;

:

mysql> -- query wanted
mysql> UPDATE test t1 INNER JOIN
    -> (SELECT *, @id := @id + 1 AS new_id
    ->  FROM test CROSS JOIN (SELECT @id := 0) param
    ->  ORDER BY city, start_date) t2
    ->  ON t1.city = t2.city AND t1.start_date = t2.start_date
    ->  INNER JOIN
    -> (SELECT *, @id2 := @id2 + 1 AS new_id
    ->  FROM test CROSS JOIN (SELECT @id2 := 0) param
    ->  ORDER BY city, start_date) t3
    ->  ON t2.new_id + 1 = t3.new_id AND t2.city = t3.city
    -> SET t1.end_date = DATE_SUB(t3.start_date, INTERVAL 1 SECOND)
    -> WHERE t1.end_date = '0001-01-01 00:00:00';
rom tesQuery OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> select * from test;
+----+--------+---------------------+---------------------+
| id | city   | start_date          | end_date            |
+----+--------+---------------------+---------------------+
| 13 | Paris  | 1995-01-01 00:00:00 | 1997-10-01 23:59:59 |
| 14 | Paris  | 1997-10-02 00:00:00 | 2013-01-24 23:59:59 |
| 15 | Paris  | 2013-01-25 00:00:00 | 2015-04-24 23:59:59 |
| 16 | Paris  | 2015-04-25 00:00:00 | 0001-01-01 00:00:00 |
| 17 | Berlin | 2014-11-01 00:00:00 | 2014-05-31 23:59:59 |
| 18 | Berlin | 2014-06-01 00:00:00 | 2015-09-10 23:59:59 |
| 19 | Berlin | 2015-09-11 00:00:00 | 2015-09-30 23:59:59 |
| 20 | Berlin | 2015-10-01 00:00:00 | 0001-01-01 00:00:00 |
| 21 | Milan  | 2001-01-01 00:00:00 | 2005-10-01 23:59:59 |
| 22 | Milan  | 2005-10-02 00:00:00 | 2006-10-02 23:59:59 |
| 23 | Milan  | 2006-10-03 00:00:00 | 2015-04-24 23:59:59 |
| 24 | Milan  | 2015-04-25 00:00:00 | 0001-01-01 00:00:00 |
+----+--------+---------------------+---------------------+
12 rows in set (0.00 sec)

A query that only works for some cases

SQL:

:

More articles: