I have the following data in one table:
Time, Type, Kilometers
12:00, 1, 0.1
12:30, 2, 0.2
14:00, 1, 0.4
15:00, 2, 1.0
16:00, 1, 1.2
16:30, 2, 1.5
16:45, 1, 2.0
This data is sorted chronologically using the DateTime field. I would like to show these record 'pairs' as 1 row, like so:
StartTime, Type1Km, Type2Km
12:00, 0.1, 0.2
14:00, 0.4, 1.0
16:00, 1.2, 1.5
16:45, 2.0, NULL
There are a couple of caveats: If there is no Type1 to start, then show NULL in the resulting tables' Type1Km field. Similarly, if there is no Type2 end, show NULL in records' Type2Km field.
How could i do this?
Unfortunately, MySQL lacks a FULL OUTER JOIN
, so you'll have to UNION
two sets together.
This will get you the cases where Type1Km
exists, whether or not Type2Km
does.
SELECT
t1.`Time` as StartTime,
t1.`Kilometers` as Type1Km,
t2.`Kilometers` as Type2Km
FROM `times` t1
LEFT JOIN `times` t2 ON t2.`Type` = 2
AND t2.`Time` = (SELECT `Time` FROM `times`
WHERE `Time` > t1.`Time`
ORDER BY `Time` LIMIT 1)
WHERE t1.`Type` = 1
Now we need the cases where Type1Km
does not exist.
SELECT
t2.`Time` as StartTime,
NULL as Type1Km,
t2.`Kilometers` as Type2Km
FROM `times` t2
LEFT JOIN `times` t1 ON t1.`Time` = (SELECT `Time` FROM `times`
WHERE `Time` < t2.`Time`
ORDER BY `Time` DESC LIMIT 1)
WHERE t2.`Type` = 2
AND (t1.`Type` = 2 OR t1.`Type` IS NULL)
UNION
those together, and you have the desired result:
(
SELECT
t1.`Time` as StartTime,
t1.`Kilometers` as Type1Km,
t2.`Kilometers` as Type2Km
FROM `times` t1
LEFT JOIN `times` t2 ON t2.`Type` = 2
AND t2.`Time` = (SELECT `Time` FROM `times`
WHERE `Time` > t1.`Time`
ORDER BY `Time` LIMIT 1)
WHERE t1.`Type` = 1
) UNION ALL (
SELECT
t2.`Time` as StartTime,
NULL as Type1Km,
t2.`Kilometers` as Type2Km
FROM `times` t2
LEFT JOIN `times` t1 ON t1.`Time` = (SELECT `Time` FROM `times`
WHERE `Time` < t2.`Time`
ORDER BY `Time` DESC LIMIT 1)
WHERE t2.`Type` = 2
AND (t1.`Type` = 2 OR t1.`Type` IS NULL)
)
ORDER BY `StartTime`
Update
In my previous query, I forgot to account for having a "type 2" record at the very beginning. Updated to account for that. Here's the results I get:
Data in times
table:
+----------+------+------------+
| Time | Type | Kilometers |
+----------+------+------------+
| 11:00:00 | 2 | 0.1 |
| 12:00:00 | 1 | 0.1 |
| 12:30:00 | 2 | 0.2 |
| 14:00:00 | 1 | 0.4 |
| 14:30:00 | 1 | 0.8 |
| 15:00:00 | 2 | 1.0 |
| 15:30:00 | 2 | 0.2 |
| 16:00:00 | 1 | 1.2 |
| 16:30:00 | 2 | 1.5 |
| 16:45:00 | 1 | 2.0 |
+----------+------+------------+
Results of query:
+-----------+---------+---------+
| StartTime | Type1Km | Type2Km |
+-----------+---------+---------+
| 11:00:00 | NULL | 0.1 |
| 12:00:00 | 0.1 | 0.2 |
| 14:00:00 | 0.4 | NULL |
| 14:30:00 | 0.8 | 1.0 |
| 15:30:00 | NULL | 0.2 |
| 16:00:00 | 1.2 | 1.5 |
| 16:45:00 | 2.0 | NULL |
+-----------+---------+---------+
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With