Ciao,
partendo dalla tabella delle registrazioni "checkinout" composta da 3 campi: Userid, CheckTime e CheckType, ho realizzato 3 query:
Le prime 2 mi raggruppano le entrate e le uscite e le ho denominate QInOut e QInOut2
QInOut
select `a`.`Userid` AS `Userid`,cast(`a`.`CheckTime` as date) AS `DataInOut`,cast(`a`.`CheckTime` as time) AS `Ingresso`,cast(coalesce((select min(`b`.`CheckTime`) from `checkinout` `b` where ((`a`.`Userid` = `b`.`Userid`) and (cast(`a`.`CheckTime` as date) = cast(`b`.`CheckTime` as date)) and (`b`.`CheckTime` >= `a`.`CheckTime`) and (`b`.`CheckType` = 1))),NULL) as time) AS `Uscita` from `checkinout` `a` where (`a`.`CheckType` = 0) order by `a`.`CheckTime`,`a`.`Userid`
QInOut2
select `c`.`Userid` AS `Userid`,cast(`c`.`CheckTime` as date) AS `DataInOut`,cast(coalesce((select min(`d`.`CheckTime`) from `V_Record` `d` where ((`c`.`Userid` = `d`.`Userid`) and (cast(`c`.`CheckTime` as date) = cast(`d`.`CheckTime` as date)) and (`d`.`CheckTime` <= `c`.`CheckTime`) and (`d`.`CheckType` = 0))),NULL) as time) AS `Ingresso`,cast(`c`.`CheckTime` as time) AS `Uscita` from `checkinout` `c` where (`c`.`CheckType` = 1) order by `c`.`CheckTime` desc,`c`.`Userid`
La terza invece effettua l'unione delle due precedenti e mi consente di avere su 2 colonne l'orario di ingresso e uscita, da qui poi puoi effettuare i vari calcoli sulle ore lavorate
QIngressiUscite:
select `QInOut`.`Userid` AS `Userid`,`QInOut`.`DataInOut` AS `DataInOut`,`QInOut`.`Ingresso` AS `Ingresso`,`QInOut`.`Uscita` AS `Uscita` from `QInOut` union all select `QInOut2`.`Userid` AS `Userid`,`QInOut2`.`DataInOut` AS `DataInOut`,`QInOut2`.`Ingresso` AS `Ingresso`,`QInOut2`.`Uscita` AS `Uscita` from `QInOut2` where (isnull(`QInOut2`.`Ingresso`) or isnull(`QInOut2`.`Uscita`)) order by `DataInOut`
Spero di esserti stato utile
Ciao !