database - MySQL any fix for query? -

i'm trying code timekeeping-software , need fetch data mysql-database, current query (below) somehow flawed:

a table called timekeeping has following columns:
1. name of employee varchar,
2. name of event ("checkin", "checkout") varchar,
3. timestamp of event

i (want to) retrieve table table gives me following columns:
1. name of employee varchar/string
2. check-in-timestamp timestamp
3. check-out-timestamp timestamp
4. hours (of time difference) int
5. remaining minutes (of time difference) int

if there check-in, no check-out before next check-in:
the check-out-column-entry shall null (so employer sees employee forgot check-out).

if there check-out, no check-in right before check-out:
the check-in-column-entry shall null (so employer sees employee forgot check-in).

problem:
if employee checks in, works till noon , checks out break later checks in , checks out after while current query calculates time difference of first check-in , last check-out wrong...

also: calculates these if these check-ins , check-outs happen within 1 date, not big of issue (but imagine timekeeping right before midnight , right after midnight... impossible)

also check-ins , check-outs inbetween first check-in , check-out not displayed anywhere (so missing rows)...

my current (wrong) query:

select employee,         min(case when event = 'checkin' timestamp end) checkin,        max(case when event = 'checkout' timestamp end) checkout,        timestampdiff(hour,               min(case when event = 'checkin' timestamp end),              max(case when event = 'checkout' timestamp end)        ) hours,        mod(timestampdiff(minute,              min(case when event = 'checkin' timestamp end),              max(case when event = 'checkout' timestamp end)        ), 60) minutes timekeeping t employee 'bobby brown' group date(timestamp) order timestamp desc; 

edit:

example:
lets employee checks in @ 6 o'clock, works, goes away break @ 12 doesn't check-out, comes @ 13 , checks in, works till 15 , checks out. query return 1 entry 6 15

i don't these kinds of subqueries, worth shot:

select t_in.employee , t_in.timestamp checkin , (select min(t_out.timestamp)     timekeeping t_out    t_out.employee = t_in.employee       , t_out.timestamp > t_in.timestamp      , t_out.event = 'checkout'   ) checkout timekeeping t_in t_in.employee 'bobby brown'    , t_in.event = 'checkin' order checkin ; 

and timespan, this:

select employee, checkin, checkout   , ((time_to_sec(checkout) - time_to_sec(checking)) / 60)     + (datediff(date(checkout), date(checkin)) * 60 * 24)      `minutes` (   [the query above minus ;] ) subq ; 

edit: added actual minutes calculation; note 60 * 24 number of minutes in day (the time_to_sec() subtraction portion negative if crossing midnight).

edit2: should in-in-out scenario.

( select t_in.employee , t_in.timestamp checkin , (select if(t_out.event = 'checkout', t_out.timestamp, null)     timekeeping t_out    t_out.employee = t_in.employee       , t_out.timestamp > t_in.timestamp    order t_out.timestamp asc    limit 1   ) checkout timekeeping t_in t_in.employee 'bobby brown'    , t_in.event = 'checkin' order checkin ) sub1 union ( select t_out2.employee , (select if(t_in2.event = 'checkin', t_in2.timestamp, null)     timekeeping t_in2    t_in2.employee = t_out2.employee       , t_in2.timestamp < t_out2.timestamp    order t_in2.timestamp desc    limit 1) checkin , t_out2.timestamp checkout  timekeeping t_out2 t_out2.employee 'bobby brown'    , t_out2.event = 'checkout' having checkin null ) sub2 ; 

last edit done on tablet, hope right. ;)