Start and end dates in different rows

Question

I have event records stored in a single table, start and end date-times are recorded as individual records.

Table where records are stored.

CREATE TABLE `avl_data` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`imei_number` bigint(20) unsigned NOT NULL DEFAULT '0',
`latitude` double NOT NULL DEFAULT '0',
`longitude` double NOT NULL DEFAULT '0',
`report_id` tinyint(4) NOT NULL DEFAULT '0',
`rtc_date` datetime NOT NULL,
`ibutton_id` varchar(25) DEFAULT 'N/A',
`odometer` bigint(20) NOT NULL DEFAULT '0',
`speed` smallint(6) NOT NULL DEFAULT '0',
`vin_number` varchar(255) DEFAULT 'N/A',
`date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`time_report` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `imei_number` (`imei_number`),
KEY `imei_rtc` (`imei_number`,`rtc_date`),
CONSTRAINT `avl_data_ibfk_1` FOREIGN KEY (`imei_number`) REFERENCES `assets` (`imei_number`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=64916466 DEFAULT CHARSET=utf8 |`

This is what I have tried so far.

    select concat(ass.label_1, " ", ass.label_2, " ", ass.label_3)                                         as "Vehicle",
       @start := case
                   when a.report_id = 103 then convert_tz(a.rtc_date, 'UTC', 'Asia/Kuwait') end as "Start",
       @end := case
                 when a.report_id = 104 then convert_tz(a.rtc_date, 'UTC', 'Asia/Kuwait') end   as "End",
       TIMEDIFF(@start, @end)                                                                   as 'Duration',
       a.speed                                                                                  as 'Speed',
       a.latitude                                                                               as 'Latitude',
       a.longitude                                                                              as 'Longitude'
from avl_data a
       inner join assets ass on a.imei_number = ass.imei_number
where a.imei_number = 356158069811103
  and rtc_date >= '2018-10-01 00:00:00'
  and rtc_date <= '2018-10-31 23:59:59'
  and a.report_id in (103, 104)
order by a.rtc_date asc;

which produces start and event records seperatly the first record is event start and second record is event stop.

+-------------------------------+---------------------+---------------------+----------+-------+-----------+-----------+
| Vehicle                       | Start               | End                 | Duration | Speed | Latitude  | Longitude |
+-------------------------------+---------------------+---------------------+----------+-------+-----------+-----------+
| Mitsubishi Outlander 14/74080 | 2018-10-01 08:29:26 | NULL                | NULL     |   128 | 29.045856 | 48.113764 |
| Mitsubishi Outlander 14/74080 | NULL                | 2018-10-01 08:30:17 | NULL     |   114 | 29.031169 | 48.121516 |
|

Ideally I would like a single row i.e

+-------------------------------+---------------------+---------------------+----------+-------+-----------+-----------+
| Vehicle                       | Start               | End                 | Duration | Speed | Latitude  | Longitude |
+-------------------------------+---------------------+---------------------+----------+-------+-----------+-----------+
| Mitsubishi Outlander 14/74080 | 2018-10-01 08:29:26 | 2018-10-01 08:30:17 | 00:01:17 |   128 | 29.045856 | 48.113764 |

This worked for me thanks to @Thorsten the lead function worked perfect.

select a.report_id as "ID",
       any_value(case when a.report_id = 103 then concat(ass.label_1, ' ', ass.label_2, ' ', ass.label_3) end) as "Vehicle",
       any_value(case when a.report_id = 103 then convert_tz(a.rtc_date, 'UTC', 'Asia/Kuwait') end) as "Start",
       any_value(case when a.report_id = 103 then lead(convert_tz(a.rtc_date, 'UTC', 'Asia/Kuwait')) over () end) as "End",
       any_value(case when a.report_id = 103 then SEC_TO_TIME(TIMESTAMPDIFF(SECOND , convert_tz(a.rtc_date, 'UTC', 'Asia/Kuwait'), lead(convert_tz(a.rtc_date, 'UTC', 'Asia/Kuwait')) over ())) end) as "Duration",
       any_value(case when a.report_id = 103 then a.speed end) as "Speed",
       any_value(case when a.report_id = 103 then a.latitude end) as "Latitude",
       any_value(case when a.report_id = 103 then a.longitude end) as "Longitude"
from avl_data a
       join assets ass on a.imei_number = ass.imei_number
where a.imei_number = 356158069811103
  and a.rtc_date >= '2018-10-01 00:00:00'
  and a.rtc_date <= '2018-10-31 23:59:59'
  and a.report_id in (103, 104);

Result, although is there a way to drop the now empty rows?

+-----+-------------------------------+---------------------+---------------------+----------+-------+-----------+-----------+
| ID  | Vehicle                       | Start               | End                 | Duration | Speed | Latitude  | Longitude |
+-----+-------------------------------+---------------------+---------------------+----------+-------+-----------+-----------+
| 103 | Mitsubishi Outlander 14/74080 | 2018-10-01 08:29:26 | 2018-10-01 08:30:17 | 00:00:51 |   128 | 29.045856 | 48.113764 |
| 104 | NULL                          | NULL                | NULL                | NULL     |  NULL |      NULL |      NULL |
| 103 | Mitsubishi Outlander 14/74080 | 2018-10-01 08:43:45 | 2018-10-01 08:44:14 | 00:00:29 |   136 | 29.067756 | 48.110384 |
| 104 | NULL                          | NULL                | NULL                | NULL     |  NULL |      NULL |      NULL |
| 103 | Mitsubishi Outlander 14/74080 | 2018-10-01 09:31:36 | 2018-10-01 09:31:44 | 00:00:08 |   135 | 29.056563 | 48.108851 |
| 104 | NULL                          | NULL                | NULL                | NULL     |  NULL |      NULL |      NULL |
| 103 | Mitsubishi Outlander 14/74080 | 2018-10-01 09:32:02 | 2018-10-01 09:33:54 | 00:01:52 |   149 | 29.048803 | 48.112581 |
| 104 | NULL                          | NULL                | NULL                | NULL     |  NULL |      NULL |      NULL |
| 103 | Mitsubishi Outlander 14/74080 | 2018-10-01 09:41:57 | 2018-10-01 09:42:35 | 00:00:38 |   131 | 29.036886 | 48.108733 |
| 104 | NULL                          | NULL                | NULL                | NULL     |  NULL |      NULL |      NULL |
| 103 | Mitsubishi Outlander 14/74080 | 2018-10-01 19:48:09 | 2018-10-01 19:48:20 | 00:00:11 |   126 | 29.034386 | 48.119706 |
| 104 | NULL                          | NULL                | NULL                | NULL     |  NULL |      NULL |      NULL |

Most people here want sample data etc as formatted text, not as images (or links to images.) — jarlh, Nov 15 '18 at 08:32
Welcome to Stack Overflow! Please provide a relevant and minimal sample data showcasing your requirements, and expected output. Refer this link on how to frame a good SQL question: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/q/333952/2469308) — Madhur Bhaiya, Nov 15 '18 at 08:33
PS: why can't you group the results by all columns and select `MIN(CASE WHEN when a.report_id = 103 then rtcdate end)` and `MIN(CASE WHEN when a.report_id = 104 then rtcdate end)` — Salman A, Nov 15 '18 at 09:52
By what rule do you decide which vehicle, speed, latitude and longitude to show in your single desired result row? Is it always exactly two rows, one with a start and one with an end, that you get with your current query? If not, what to do in case of more rows? — Thorsten Kettner, Nov 15 '18 at 10:03
Only need the End date-time from the End record all other information from the second record can be discarded, I use the in clause so only start and end records returned. — Martyn, Nov 15 '18 at 10:16

PhilS · Answer 1 · 2018-11-15T15:36:38.890

Do you have a common key between the 2 rows?

If so then simply join 2 instances of the avl_data table together

Something like

select convert_tz(aStart.rtc_date, 'UTC', tzone) as "Start", 
    convert_tz(aEnd.rtc_date, 'UTC', tzone) as "End",
from avl_data aStart
inner join avl_data aEnd on aStart.Key = aEnd.Key;

where Key is the common key.

If you do not have a common key and are relying on one row following another then and you have a primary key sequence then something like this may work

select convert_tz(aStart.rtc_date, 'UTC', tzone) as "Start", 
    convert_tz(aEnd.rtc_date, 'UTC', tzone) as "End",
from avl_data aStart
inner join avl_data aEnd on aStart.reportId + 1 = aEnd.reportId;

however I would not recommend it as its likely that having one Id greater by one than the previous is not 100% reliable for the whole table.

Something like this may give you the answer but its going to have extra results as it will have ones that start with the End as well

select aStart.imei_number as "Imei",
        convert_tz(aStart.rtc_date, 'UTC', tzone) as "Start", 
        convert_tz(MIN(aEnd.rtc_date), 'UTC', tzone) as "End"
from avl_data aStart
inner join avl_data aEnd on aStart.imei_number = aEnd.imei_number
                    and  aStart.rtc_date < aEnd.rtc_date
group by aStart.imei_number, aStart.rtc_date, aStart.report_id
order by aStart.imei_number, aStart.rtc_date;

You could use this as a subquery and wrap it with a ranking value ref https://stackoverflow.com/a/1895127/3805124 then remove the even ones as they should be the extra ones starting with an end record.

Hi PhilS I don't have a common key between the rows unfortunately, also I cannot use the id's as they could well not be sequential as you mention — Martyn, Nov 15 '18 at 09:15
I've added another query that may get you some of the way to the answer. — PhilS, Nov 15 '18 at 10:45

score 0 · Accepted Answer · answered Nov 15 '18 at 11:22

You can use an aggregation to get one row out of the two. As you want some values from report_id = 103 and some from report_id = 104, you'd use CASE WHEN to get one value or the other. And as there is only one 103 row and one 104 row, your aggregation function is a pseudo aggregation with ANY_VALUE.

select
  any_value(case when a.report_id = 103 then concat(ass.label_1, ' ', ass.label_2, ' ', ass.label_3) end) as "Vehicle",
  any_value(case when a.report_id = 103 then convert_tz(a.rtc_date, 'UTC', 'Asia/Kuwait') end) as "Start",
  any_value(case when a.report_id = 104 then convert_tz(a.rtc_date, 'UTC', 'Asia/Kuwait') end) as "End",
  TIMEDIFF(
    any_value(case when a.report_id = 103 then convert_tz(a.rtc_date, 'UTC', 'Asia/Kuwait') end),
    any_value(case when a.report_id = 104 then convert_tz(a.rtc_date, 'UTC', 'Asia/Kuwait') end)
  ) as "Duration",
  any_value(case when a.report_id = 103 then a.speed end) as "Speed",
  any_value(case when a.report_id = 103 then a.latitude end) as "Latitude",
  any_value(case when a.report_id = 103 then a.longitude end) as "Longitude"
from avl_data a
join assets ass on a.imei_number = ass.imei_number
where a.imei_number = 356158069811103
  and rtc_date >= date '2018-10-01'
  and rtc_date < date '2018-11-01'
  and a.report_id in (103, 104);

The alternative would be a join:

select
  a103."Vehicle",
  a103."Start",
  a104."End",
  TIMEDIFF(a103."Start", a104."End") as "Duration",
  a103."Latitude",
  a103."Longitude",
from
(
  select
    concat(ass.label_1, ' ', ass.label_2, ' ', ass.label_3) as "Vehicle",
    convert_tz(a.rtc_date, 'UTC', 'Asia/Kuwait') end as "Start",
    speed as "Speed",
    latitude as "Latitude",
    longitude as "Longitude"
  from avl_data a
  join assets ass on a.imei_number = ass.imei_number
  where a.imei_number = 356158069811103
    and rtc_date >= '2018-10-01 00:00:00'
    and rtc_date <= '2018-10-31 23:59:59'
    and a.report_id = 103
) a103
cross join
(
  select convert_tz(a.rtc_date, 'UTC', 'Asia/Kuwait' as "End"
  from avl_data a
  join assets ass on a.imei_number = ass.imei_number
  where a.imei_number = 356158069811103
    and rtc_date >= '2018-10-01 00:00:00'
    and rtc_date <= '2018-10-31 23:59:59'
    and a.report_id = 104
) a104;

Thanks @Thorsten but this seems to still leave the end date-time on the second row — Martyn, Nov 15 '18 at 12:10
Which query? **The first query results in one row only**, because it is aggregating (`ANY_VALUE`) without a `GROUP BY` clause. **The second query returns one row only**, because you said there is only one 103 row and one 104 row in your results. Cross joining 1 x 1 gives 1. — Thorsten Kettner, Nov 15 '18 at 12:16
I do apologise if I mislead you there are multiple 103, 104, in the result. — Martyn, Nov 15 '18 at 12:44
Then which 103 row to combine with which 104 row? Or must we ignore certain rows? If so, which? — Thorsten Kettner, Nov 15 '18 at 13:14
Every other row seems to hold the end date needed for the previous row. — Martyn, Nov 15 '18 at 13:57
Seems to? If you don't *know*, you cannot write a valid query. So there is nothing else to indicate that a 104 belongs to a 103. There will never be two 103 without a 104 inbetween. Correct? Then what MySQL version are you using. As of MySQL 8 you can use `LAG` or `ROW_NUMBER` for this. In older versions this gets messy. — Thorsten Kettner, Nov 15 '18 at 14:08
Thank you @Thorsten I will take a look at those, do you have any examples? — Martyn, Nov 15 '18 at 16:33

Start and end dates in different rows

2 Answers2