MySQL: How to get maintenance schedule from mileage periods

Question

I have a MySQL table describing the maintenance cycle of railway vehicles (type of overhaul and period in kilometers):

CREATE TABLE `cycle_test` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `type` varchar(2) COLLATE utf8_spanish_ci NOT NULL,
 `km` int(11) unsigned NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci

I have populated this table as follows:

INSERT INTO `cycle_test` (`id`, `type`, `km`) VALUES (NULL, 'R1', '12000'), (NULL, 'R2', '24000'), (NULL, 'R3', '72000'), (NULL, 'R4', '144000');
SELECT type, km FROM cycle_test;
+------+--------+
| type | km     |
+------+--------+
| R1   |  12000 |
| R2   |  24000 |
| R3   |  72000 |
| R4   | 144000 |
+------+--------+

The goal is to obtain the maintenance schedule, with all the overhauls to be carried out throughout the cycle, and the mileage of each overhaul, taking into account that, when overhauls of different types coincide, the one with the highest rank (R4 > R3 > R2 > R1) supersedes the ones with the lowest rank, as shown below:

+--------+------+
| km     | type |
+--------+------+
| 12000  | R1   |
| 24000  | R2   |
| 36000  | R1   |
| 48000  | R2   |
| 60000  | R1   |
| 72000  | R3   |
| 84000  | R1   |
| 96000  | R2   |
| 108000 | R1   |
| 120000 | R2   |
| 132000 | R1   |
| 144000 | R4   |
+--------+------+

this problem I have already solved in PHP, but I am looking for a purely MySQL solution, either with session variables or stored procedures. The (possible) solution must be scalable for different maintenance cycles (not hardcoded). Thanks in advance for your help and/or hints.

Answer 1

Well, with the help of the MySQL docs, it has turned out easier than I thought. First, I have created the schedule table (in production, it will be a temporary table):

CREATE TABLE `schedule_test` (
 `type` varchar(2) COLLATE utf8_spanish_ci NOT NULL,
 `km` int(11) unsigned NOT NULL,
 UNIQUE KEY `km` (`km`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci

The UNIQUE KEY km allows detecting the concurrence of overhauls of different ranks with the same mileage.

Then I have created the following stored procedure in the database:

BEGIN
/* Declaration of variables, cursors and handlers */
DECLARE done INT DEFAULT 0;
DECLARE vTip CHAR(16);
DECLARE vKm, acKm INT;
DECLARE vFinal INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT type, km FROM cycle_test;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
/* end declaration */
TRUNCATE schedule_test; -- Get rid of any data
SELECT MAX(km) FROM cycle_test INTO vFinal; -- Find the final mileage of the cycle
OPEN cur1;
REPEAT
    FETCH cur1 INTO vTip, vKm; -- Read record from overhaul cycle
    IF NOT done THEN
        SET acKm = 0; -- Initialize mileage
        REPEAT
            SET acKm = acKm + vKm; -- Accumulate mileage
            INSERT INTO schedule_test (type, km) VALUES (vTip, acKm)
            ON DUPLICATE KEY UPDATE type=VALUES(type); -- Insert schedule; update type if an overhaul already exists
        UNTIL acKm = vFinal END REPEAT; -- until reach the end of cycle
    END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END

And this is the result I wanted, after running the procedure:

mysql> select * from schedule_test;
+------+--------+
| type | km     |
+------+--------+
| R1   |  12000 |
| R2   |  24000 |
| R1   |  36000 |
| R2   |  48000 |
| R1   |  60000 |
| R3   |  72000 |
| R1   |  84000 |
| R2   |  96000 |
| R1   | 108000 |
| R2   | 120000 |
| R1   | 132000 |
| R4   | 144000 |
+------+--------+
12 rows in set (0.00 sec)