In or out at a 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,  '2014-05-15', '09:00:00', 'I' ),

(1,  '2014-05-15', '11:00:00', 'O' ),

(1,  '2014-05-15', '12:30:00', 'I' ),

(1,  '2014-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='2014-5-15'

  and a.clocktime<='12:30:00' and a.clocktype='I'

  and b.clocktime>'12:30:00' and b.clocktype='O';

+---------+

| InStore |

+---------+

| Yes     |

+---------+




Return to the Artful Common Queries page