combine results of 2 rows with start on row1 and end on row2

Question

I have this on a MariaDB version 5.5.65

datetimeconnect datetimeend queue   agent   event
02/05/2022 10:02:31 02/05/2022 10:02:31 "5009"  "Mikey Blogs"   "ADDMEMBER"
02/05/2022 15:53:58 02/05/2022 15:53:58 "5009"  "Mikey Blogs"   "REMOVEMEMBER"
02/05/2022 16:42:28 02/05/2022 16:42:28 "5005"  "Mikey Blogs"   "ADDMEMBER"
02/05/2022 18:02:45 02/05/2022 18:02:45 "5005"  "Mikey Blogs"   "REMOVEMEMBER"
03/05/2022 10:01:33 03/05/2022 10:01:33 "5009"  "Mikey Blogs"   "ADDMEMBER"
03/05/2022 16:01:22 03/05/2022 16:01:22 "5009"  "Mikey Blogs"   "REMOVEMEMBER"

the first row with 'ADDMEMBER' is the start and the second 'REMOVEMEMBER' is the end.. so I need to show, on each row: Start (row1) end(row2), queue, name, and datediff (end, start) to show the duration..

I admin I never had to combine rows before.. If anyone could assist, I greatly appreciate. Have a nice day

Addition: Tge output I would like to see is (one a single row): start date (row1) ADDMEMBER, end date (row 2) REMOVEMEMBER, diference in hh:mm:ss, name, queue number. and show create table result:

CREATE TABLE `queue_stats_mv` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `datetime` timestamp NOT NULL DEFAULT '1971-01-01 00:00:00',
    `datetimeconnect` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `datetimeend` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `queue` varchar(100) NOT NULL DEFAULT '',
    `agent` varchar(100) NOT NULL DEFAULT '',
    `event` varchar(40) NOT NULL DEFAULT '',
    `uniqueid` varchar(50) NOT NULL DEFAULT '',
    `real_uniqueid` varchar(50) NOT NULL,
    `clid` varchar(50) NOT NULL DEFAULT '',
    `url` varchar(100) NOT NULL DEFAULT '',
    `did` varchar(100) NOT NULL DEFAULT '',
    `position` int(6) unsigned NOT NULL DEFAULT '1',
    `info1` varchar(50) NOT NULL DEFAULT '',
    `info2` varchar(50) NOT NULL DEFAULT '',
    `info3` varchar(50) NOT NULL DEFAULT '',
    `info4` varchar(50) NOT NULL DEFAULT '',
    `info5` varchar(50) NOT NULL DEFAULT '',
    `overflow` int(6) unsigned NOT NULL DEFAULT '1',
    `combined_waittime` int(11) unsigned NOT NULL DEFAULT '0',
    `waittime` int(11) unsigned NOT NULL DEFAULT '0',
    `talktime` int(11) unsigned NOT NULL DEFAULT '0',
    `ringtime` int(11) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    KEY `fecha` (`datetime`),
    KEY `ev` (`event`),
    KEY `pidx` (`real_uniqueid`,`id`),
    KEY `uni` (`uniqueid`),
    KEY `runi` (`real_uniqueid`)
) ENGINE=InnoDB AUTO_INCREMENT=1209991 DEFAULT CHARSET=utf8

Answer 1

On newer versions of mariadb (10.2 or higher) you would use lag or lead window functions in a subquery for this, which will likely be quite a bit more efficient. In your very old version, the easiest way is to do a self join to find the following event (joining all following events, but using where conditions to ensure a removemember is found and that it is in fact the immediately following event):

select addmember.queue, addmember.agent, addmember.datetimeconnect, removemember.datetimeconnect
from queue_stats_mv addmember
join queue_stats_mv removemember on
    removemember.queue=addmember.queue and
    removemember.agent=addmember.agent and
    removemember.event in ('ADDMEMBER','REMOVEMEMBER') and
    removemember.datetimeconnect > addmember.datetimeconnect
where
    addmember.event='ADDMEMBER' and
    removemember.event='REMOVEMEMBER' and
    not exists (
        select 1
        from queue_stats_mv inbetween
        where
            inbetween.queue=addmember.queue and
            inbetween.agent=addmember.agent and
            inbetween.event in ('ADDMEMBER','REMOVEMEMBER') and
            inbetween.datetimeconnect between addmember.datetimeconnect and removemember.datetimeconnect
    )

You will need a composite index on (queue,agent,event,datetimeconnect) (or some variation of order as long as the time is last), though event only needs to be included if there are lots of other events than the addmember and removemember.

If there is some maximum duration expected, you can improve the query by adding that to the join on clause, e.g.:

and removemember.datetimeconnect <= addmember.datetimeconnect + interval 1 day

I am assuming you never have multiple events for the same queue/agent with the same datetimeconnect; that is possible to make work, but would require you to show your schema.

A more efficient way would be to emulate the lag window function using variables, but that's only worth doing if the easy way is not efficient enough.

Since you have non-UTC times, datediff will not calculate the duration correctly in all cases, but that is better off being addressed in a separate question.