In/out at given date and time

from the Artful Common Queries page

Employees punch in and out. You track these events with ... 
drop table if exists tbl;
create table tbl( empno smallint, clockdate date, clocktime time, clocktype char(1) );
insert into tbl values 
(1,  '2024-05-15', '09:00:00', 'I' ),
(1,  '2024-05-15', '11:00:00', 'O' ),
(1,  '2024-05-15', '12:30:00', 'I' ),
(1,  '2024-05-15', '19:00:00', 'O' );
Was employee 1 in or out at 12:30pm on 15 May 2014? Use a self-join to play the in and out events against the given datetime ... 
select if(count(1),'Yes','No' ) as InStore 
from tbl a
join tbl b using(empno, clockdate)
where empno=1
  and a.clockdate='2024-5-15'
  and a.clocktime<='12:30:00' and a.clocktype='I'
  and b.clocktime>'12:30:00' and b.clocktype='O';
+---------+
| InStore |
+---------+
| Yes     |
+---------+

Last updated 10 Jun 2024