select
signDate,
min(signTime) as signTime,
signType,
tel
from (
select EMPLOYEENO,
substr (checktime,0,10) as signDate,
substr (checktime,12,9) as signTime,
case APM
when 'AM' then 1
when 'PM' then 2
end as signType,
tel
from(
select EMPLOYEENO ,
to_char(cast (cast (checktime as timestamp) as date),'yyyy-mm-dd hh24:mi:ss') as checktime ,
to_char(substr (checktime ,27,2)) APM,
substr (MOBILE,4,11) tel
from TH_FS_ATTENDANCE_RECORD
))
where signType = 1
and signDate = to_char(SYSDATE,'YYYY-MM-DD')
group by tel,signDate,signType