[SOLVED] MYSQL – Select rows matching event code and imediate next row and calculate time date difference

Issue

So this is the table

ID     Date                 Event
1547   2013-05-09 16:26:02  PA
1547   2013-05-15 13:59:23  TA
1547   2013-05-21 10:16:56  EA
1547   2013-05-21 10:17:27  PM
1547   2014-01-16 11:42:12  IH
1547   2014-01-16 11:42:13  RP
1547   2014-01-21 10:01:18  MP <-
1547   2014-12-05 14:32:21  RE <-
1547   2014-12-05 14:34:24  RE
1666   2013-05-29 11:26:38  PA
1666   2013-06-04 13:38:42  TA
1666   2013-06-05 14:16:13  EA
1666   2013-08-21 10:07:08  PR
1666   2013-08-21 10:38:51  TR
1666   2013-08-21 10:38:52  MP <-
1666   2013-10-07 16:26:46  PM <-
1666   2013-10-09 14:38:51  TM
1666   2013-10-09 14:38:52  EP
1666   2013-10-25 10:29:01  IH
1666   2013-12-13 08:52:41  IH
1666   2013-12-13 08:52:43  RP
1666   2014-01-21 09:55:10  MP <-
1666   2014-05-05 15:52:34  AB <-
1666   2014-05-07 14:55:58  RD
1692   2013-06-10 14:17:17  PA
1692   2013-06-10 14:17:53  TA
1692   2013-06-10 15:01:08  EA
1692   2013-08-21 10:04:39  PR
1692   2013-08-21 10:37:38  TR
1692   2013-08-21 10:37:39  ER
1692   2013-09-26 08:33:48  PM
1692   2013-09-26 16:32:46  TM
1692   2013-09-26 16:32:47  EP
1692   2013-10-11 09:21:26  IH
1692   2013-12-19 15:29:20  IV
1692   2013-12-19 15:29:21  RP
1692   2013-12-19 15:33:19  MP <-
1692   2014-01-21 11:05:46  FX <-
1692   2014-01-22 10:16:27  RE

I want to select the first ‘MP’event for each ID, the IMMEDIATE next one and calculate the difference in days.

The result should be:

ID     Date                 DaystoNextEVENT
1547   2014-01-21 10:01:18  318
1666   2013-08-21 10:38:52  47
1666   2014-01-21 09:55:10  104
1692   2013-12-19 15:33:19  33

I have tried to use limit but always had problems limiting the next row of the same ID with the source of the date in the match.

Solution

We can use a curser in a sub-query, using order by day desc we reverse the sort order which gives us the date of the next event. If it is the same ID we then return it in the query.

SET @quot='';
SET @id=0;
select
  id,
  day,
  lag_day,
  event,
  case when id=lag_id then
     datediff(lag_day,day) end 
     as days_to_next
from
(select
  @id lag_id,
  @id:=id id,
  @quot lag_day, 
  @quot:=day day,
  event
from 
  events
order by 
  id , 
  day desc) e
where event ='MP'
order by
  id,
  day;
  id | day                 | lag_day             | event | days_to_next
---: | :------------------ | :------------------ | :---- | -----------:
1547 | 2014-01-21 10:01:18 | 2014-12-05 14:32:21 | MP    |          318
1666 | 2013-08-21 10:38:52 | 2013-10-07 16:26:46 | MP    |           47
1666 | 2014-01-21 09:55:10 | 2014-05-05 15:52:34 | MP    |          104
1692 | 2013-12-19 15:33:19 | 2014-01-21 11:05:46 | MP    |           33

db<>fiddle here

Answered By – Kendle

Answer Checked By – Clifford M. (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.