MYSQL - Select rows matching event code and imediate next row and calculate time date difference-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

For version 8 :

SELECT id,
       `date`,
       DATEDIFF(LEAD(`date`) OVER (PARTITION BY id ORDER BY `date`),  -- next date
                `date`                                                -- the date of MP
                ) DaystoNextEVENT
FROM table t1
WHERE event = 'MP'
  AND NOT EXISTS ( SELECT NULL                 -- check that there is no MP earlier
                   FROM table t2
                   WHERE t1.id = t2.id
                     AND t1.`date` > t2.`date`
                     AND t2.event = 'MP' );

For version 5.x:

SELECT t1.id,
       t1.`date`,
       DATEDIFF(t2.`date`, t1.`date`) DaystoNextEVENT
FROM table t1                                  -- row with MP
JOIN table t2 ON t1.id = t2.id                 -- next row
             AND t1.`date` < t2.`date`
WHERE t1.event = 'MP'
  AND NOT EXISTS ( SELECT NULL                 -- check that there is no MP earlier
                   FROM table t3
                   WHERE t1.id = t3.id
                     AND t1.`date` > t3.`date`
                     AND t3.event = 'MP' )
  AND NOT EXISTS ( SELECT NULL                 -- check that there is no a row
                   FROM table t4               -- between rows from tables t1 and t2
                   WHERE t1.id = t4.id
                     AND t1.`date` < t4.`date`
                     AND t4.`date` < t2.`date` );