I have some trouble, when I want make some validate the query isn't display my expectation table, so I want make some value but if the value in field A and B is different then value of table related to field B will be NULL.
my expectation table like this :
+-----------+---------------------+-------------+-------------+---------------------------+
+ no_reg + date_time_A + time_in + time_out + B.date_time date_time_B +
+-----------+---------------------+-------------+-------------+---------------------------+
+ 2019069 + 2020-05-04 07:15:00 + 07:15:00 + 21:15:00 + 2020-05-04 21:15:00 +
+ 2019069 + 2020-05-04 07:15:00 + 07:15:00 + 28:15:00 + 2020-05-04 28:15:00 +
+ 2019069 + 2020-05-04 07:15:00 + 07:15:00 + 27:15:00 + 2020-05-04 27:15:00 +
+ 2019069 + 2020-05-05 07:15:00 + 07:15:00 + 21:15:00 + 2020-05-05 21:15:00 +
+ 2019069 + 2020-05-06 07:15:00 + 07:15:00 + NULL + NULL +
+ 2019069 + NULL + NULL + 23:15:00 + 2020-05-07 23:15:00 +
+-----------+---------------------+-------------+-------------+---------------------------+
but in reality code result is :
+-----------+---------------------+-------------+-------------+---------------------------+
+ no_reg + date_time_A + time_in + time_out + B.date_time date_time_B +
+-----------+---------------------+-------------+-------------+---------------------------+
+ 2019069 + 2020-05-04 07:15:00 + 07:15:00 + 21:15:00 + 2020-05-04 21:15:00 +
+ 2019069 + 2020-05-04 07:15:00 + 07:15:00 + 28:15:00 + 2020-05-04 28:15:00 +
+ 2019069 + 2020-05-04 07:15:00 + 07:15:00 + 27:15:00 + 2020-05-04 27:15:00 +
+ 2019069 + 2020-05-05 07:15:00 + 07:15:00 + 21:15:00 + 2020-05-05 21:15:00 +
+ 2019069 + 2020-05-05 07:15:00 + 07:15:00 + 23:15:00 + 2020-05-07 23:15:00 +
+ 2019069 + 2020-05-06 07:15:00 + 07:15:00 + 23:15:00 + 2020-05-07 23:15:00 +
+-----------+---------------------+-------------+-------------+---------------------------+
for database structure :
CREATE TABLE `raw_attendance` (
`id` int(11) NOT NULL,
`no_reg` varchar(128) NOT NULL,
`date_time` datetime NOT NULL,
`time` time NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `raw_attendance` (`id`, `no_reg`, `date_time`, `time`) VALUES
(115, '2019069', '2020-05-04 07:15:00', '07:15:00'),
(116, '2019069', '2020-05-04 21:15:00', '21:15:00'),
(117, '2019069', '2020-05-05 03:15:00', '03:15:00'),
(118, '2019069', '2020-05-05 04:15:00', '04:15:00'),
(119, '2019069', '2020-05-05 07:15:00', '07:15:00'),
(120, '2019069', '2020-05-05 21:15:00', '21:15:00'),
(121, '2019069', '2020-05-06 07:15:00', '07:15:00'),
(123, '2019069', '2020-05-07 23:15:00', '23:15:00'),
(124, '2019070', '2020-05-04 07:25:00', '07:25:00'),
(125, '2019070', '2020-05-04 21:25:00', '21:25:00'),
(126, '2019070', '2020-05-05 03:35:00', '03:35:00'),
(127, '2019070', '2020-05-05 04:25:00', '04:25:00'),
(128, '2019070', '2020-05-05 07:55:00', '07:55:00'),
(129, '2019070', '2020-05-05 21:55:00', '21:55:00');
query that I use for reality result is :
SELECT A.id, A.no_reg, A.date_time date_time_A, A.time time_in,
CASE
WHEN B.time BETWEEN '12:00:00' AND '23:59:00' THEN B.time
WHEN B.time BETWEEN '00:00:00' AND '06:14:00' THEN TIMEDIFF(B.time, "-24:00:00")
END time_out, B.time test, B.date_time date_time_B
FROM raw_attendance A
LEFT JOIN raw_attendance B
ON A.no_reg = B.no_reg
AND A.date_time < B.date_time
WHERE A.time BETWEEN '06:14:00' AND '11:59:00' AND A.`no_reg` = '2019069'
HAVING
CASE
WHEN B.time BETWEEN '00:00:00' AND '06:14:00' OR B.time BETWEEN '12:00:00' AND '23:59:00' THEN 1
WHEN A.date_time <> B.date_time THEN 0 ELSE 1
END
ORDER BY A.`date_time` ASC
And for testing editor : MySQL Query Editor In db-fiddle
If my explanation is incomprehensible, I apologize, and you can ask me again, Thank You