Combining a condition from two mysql columns

Question

Combining a condition from two mysql columns

I would like to combine conditions from 2 different columns for my query. This is my initial request. You can test it at sqlfiddle.com.

-- creating database first for test data create table attendance(Id int, DateTime datetime, Door char(20)); INSERT INTO attendance VALUES ( 1, '2016-01-01 08:00:00', 'In'), ( 2, '2016-01-01 09:00:00', 'Out'), ( 3, '2016-01-01 09:15:00', 'In'), ( 4, '2016-01-01 09:30:00', 'In'), ( 5, '2016-01-01 10:00:00', 'Out'), ( 6, '2016-01-01 15:00:00', 'In'); SELECT * FROM attendance; SELECT @id: =@id +1 Id, MAX(IF(Door = 'In', DateTime, NULL)) `Check In`, MAX(IF(Door = 'Out', DateTime, NULL)) `Check Out` FROM (SELECT *, CASE WHEN (Door != 'Out' AND @last_door = 'Out') THEN @group_num: =@group _num+1 ELSE @group_num END door_group, @last_door:=Door FROM attendance JOIN (SELECT @group_num:=1,@last_door := NULL) a ) t JOIN (SELECT @id:=0) b GROUP BY t.door_group HAVING SUM(Door = 'In') > 0 AND SUM(Door = 'Out') > 0; //output +------+---------------------+---------------------+ | Id | Check In | Check Out | +------+---------------------+---------------------+ | 1 | 2016-01-01 08:00:00 | 2016-01-01 09:00:00 | | 2 | 2016-01-01 09:30:00 | 2016-01-01 10:00:00 | +------+---------------------+---------------------+

From the above query, I would like to add another column.

 -- creating database first for test data create table attendance(Id int, DateTime datetime, Door char(20), Active_door char(20)); INSERT INTO attendance VALUES ( 1, '2016-01-01 08:00:00', 'In', ''), ( 2, '2016-01-01 09:00:00', 'Out', ''), ( 3, '2016-01-01 09:15:00', 'In', ''), ( 4, '2016-01-01 09:30:00', 'In', ''), ( 5, '2016-01-01 09:35:00', '', 'On'), ( 6, '2016-01-01 10:00:00', 'Out', ''), ( 7, '2016-01-01 16:00:00', '', 'Off');

These are the changes I made to my request, but it does not work.

 SELECT * FROM attendance; SELECT @id: =@id +1 Id, MAX(IF(Door = 'In' OR Active_door = "On", DateTime, NULL)) `Check In`, MAX(IF(Door = 'Out' OR Active_door = "Off", DateTime, NULL)) `Check Out` FROM (SELECT *, CASE WHEN ((Door != 'Out' OR Active_door != "Off") AND (@last_door = 'Out' OR @last_door = 'Off')) THEN @group_num: =@group _num+1 ELSE @group_num END door_group, @last_door:=Door FROM attendance JOIN (SELECT @group_num:=1,@last_door := NULL) a ) t JOIN (SELECT @id:=0) b GROUP BY t.door_group HAVING SUM(Door = 'In') > 0 OR SUM(Active_door = 'On') > 0 AND SUM(Door = 'Out') > 0 OR SUM(Active_door = 'Off') > 0; //output +------+---------------------+---------------------+ | Id | Check In | Check Out | +------+---------------------+---------------------+ | 1 | 2016-01-01 08:00:00 | 2016-01-01 09:00:00 | | 2 | 2016-01-01 09:35:00 | 2016-01-01 10:00:00 | | 3 | NULL | 2016-01-01 16:00:00 | +------+---------------------+---------------------+ //my desire output +------+---------------------+---------------------+ | Id | Check In | Check Out | +------+---------------------+---------------------+ | 1 | 2016-01-01 08:00:00 | 2016-01-01 09:00:00 | | 2 | 2016-01-01 09:35:00 | 2016-01-01 16:00:00 | +------+---------------------+---------------------+

Please help me guys, how can I get the desired result. I would like to get the last and last of both columns. Thank you in advance.

+3

mysql mysql-variables

Eddy Jun 21 '16 at 9:12

source share

1 answer

Drew · Answer 1 · 2016-06-21T11:59:51+0000

This allows you to keep the solution easily supported without completing the final request in just one shot, which would almost double its size (in my opinion). This is due to the fact that the results must correspond and are presented on the same line with the corresponding In and Out events. Therefore, in the end I use several desktops. It is implemented in a stored procedure.

A stored procedure uses several variables that are entered using a cross join . Think of cross-joining as a mechanism for initializing variables. Variables are stored safely, so I believe that in the spirit of this document , query variables are often referenced. Important parts of the link are the safe handling of variables in a row, forcing them to be set before other columns using them. This is achieved using the greatest() and least() functions, which take precedence over variables set without using these functions. Also note that coalesce() often used for the same purpose. If their use seems strange, for example, taking the largest of the number, which is known to be greater than 0 or 0, is good, which is intentional. Designed to force the prioritization of variables.

Columns in the query with names like dummy2 , etc. - These are columns that were not used, but they were used to set variables inside, say, greatest() or another. This has been mentioned above. An output, such as 7777, was a placeholder in the third slot, since the used if() needed some value. Therefore, ignore all this.

I included some screenshots of the code as it moved through the layers to help you visualize the output. And how these iterations of development slowly add up to the next phase to expand the previous one.

I am sure that my peers could improve this in one request. I could end up like this. But I believe that this would lead to a confusing mess that would break if it touched.

Scheme:

 create table attendance2(Id int, DateTime datetime, Door char(20), Active_door char(20)); INSERT INTO attendance2 VALUES ( 1, '2016-01-01 08:00:00', 'In', ''), ( 2, '2016-01-01 09:00:00', 'Out', ''), ( 3, '2016-01-01 09:15:00', 'In', ''), ( 4, '2016-01-01 09:30:00', 'In', ''), ( 5, '2016-01-01 09:35:00', '', 'On'), ( 6, '2016-01-01 10:00:00', 'Out', ''), ( 7, '2016-01-01 16:00:00', '', 'Off'); drop table if exists oneLinersDetail; create table oneLinersDetail ( -- architect this depending on multi-user concurrency id int not null, dt datetime not null, door int not null, grpIn int not null, grpInSeq int not null, grpOut int not null, grpOutSeq int not null ); drop table if exists oneLinersSummary; create table oneLinersSummary ( -- architect this depending on multi-user concurrency id int not null, grpInSeq int null, grpOutSeq int null, checkIn datetime null, -- we are hoping in the end it is not null checkOut datetime null -- ditto );

Stored Procedure:

 DROP PROCEDURE IF EXISTS fetchOneLiners; DELIMITER $$ CREATE PROCEDURE fetchOneLiners() BEGIN truncate table oneLinersDetail; -- architect this depending on multi-user concurrency insert oneLinersDetail(id,dt,door,grpIn,grpInSeq,grpOut,grpOutSeq) select id,dt,door,grpIn,grpInSeq,grpOut,grpOutSeq from ( select id,dt,door, if(@lastEvt!=door and door=1, greatest(@grpIn: =@grpIn +1,0), 7777) as dummy2, -- this output column we don't care about (we care about the variable being set) if(@lastEvt!=door and door=2, greatest(@grpOut: =@grpOut +1,0), 7777) as dummy3, -- this output column we don't care about (we care about the variable being set) if (@lastEvt!=door,greatest(@flip:=1,0),least(@flip:=0,1)) as flip, if (door=1 and @flip=1,least(@grpOutSeq:=0,1),7777) as dummy4, if (door=1 and @flip=1,greatest(@grpInSeq:=1,0),7777) as dummy5, if (door=1 and @flip!=1,greatest(@grpInSeq: =@grpInSeq +1,0),7777) as dummy6, if (door=2 and @flip=1,least(@grpInSeq:=0,1),7777) as dummy7, if (door=2 and @flip=1,greatest(@grpOutSeq:=1,0),7777) as dummy8, if (door=2 and @flip!=1,greatest(@grpOutSeq: =@grpOutSeq +1,0),7777) as dummy9, @grpIn as grpIn, @grpInSeq as grpInSeq, @grpOut as grpOut, @grpOutSeq as grpOutSeq, @lastEvt:=door as lastEvt from ( select id,`datetime` as dt, CASE WHEN Door='in' or Active_door='on' THEN 1 ELSE 2 END as door from attendance2 order by id ) xD1 -- derived table #1 cross join (select @grpIn:=0,@grpInSeq:=0,@grpOut:=0,@grpOutSeq:=0,@lastEvt:=-1,@flip:=0) xParams order by id ) xD2 -- derived table #2 order by id; -- select * from oneLinersDetail; truncate table oneLinersSummary; -- architect this depending on multi-user concurrency insert oneLinersSummary (id,grpInSeq,grpOutSeq,checkIn,checkOut) select distinct grpIn,null,null,null,null from oneLinersDetail order by grpIn; -- select * from oneLinersSummary; update oneLinersSummary ols join ( select grpIn,max(grpInSeq) m from oneLinersDetail where door=1 group by grpIn ) d1 on d1.grpIn=ols.id set ols.grpInSeq=d1.m; -- select * from oneLinersSummary; update oneLinersSummary ols join ( select grpOut,max(grpOutSeq) m from oneLinersDetail where door=2 group by grpOut ) d1 on d1.grpOut=ols.id set ols.grpOutSeq=d1.m; -- select * from oneLinersSummary; update oneLinersSummary ols join oneLinersDetail old on old.door=1 and old.grpIn=ols.id and old.grpInSeq=ols.grpInSeq set ols.checkIn=old.dt; -- select * from oneLinersSummary; update oneLinersSummary ols join oneLinersDetail old on old.door=2 and old.grpOut=ols.id and old.grpOutSeq=ols.grpOutSeq set ols.checkOut=old.dt; -- select * from oneLinersSummary; -- dump out the results select id,checkIn,checkOut from oneLinersSummary order by id; -- rows are left in those two tables (oneLinersDetail,oneLinersSummary) END$$ DELIMITER ;

Test:

 call fetchOneLiners(); +----+---------------------+---------------------+ | id | checkIn | checkOut | +----+---------------------+---------------------+ | 1 | 2016-01-01 08:00:00 | 2016-01-01 09:00:00 | | 2 | 2016-01-01 09:35:00 | 2016-01-01 16:00:00 | +----+---------------------+---------------------+