Microsoft SQL Server示例：

 选择  distinct  
 CE.Emp_Name，
（ select  min（Emp_Time）来自 Raw_Record 作为 MINCE 其中 MINCE.Emp_Type = ' 在' 和 MINCE.Emp_Name = CE.Emp_Name  AND   CONVERT （ varchar （ 8 ），MINCE.EMP_TIME， 112 ）= CONVERT（ varchar （ 8 ），CE.Emp_Time， 112 ）） 作为 MinTime，
（选择 max（Emp_Time）来自 Raw_Record 作为 MAXCE 其中 MAXCE.Emp_Type = '  Out' 和 MAXCE.Emp_Name = CE.Emp_name  AND   CONVERT （ varchar （ 8 ），MAXCE.EMP_TIME， 112 ）= CONVERT（ varchar （ 8 ），CE.Emp_Time， 112 ））作为 MaxTime 
来自 Raw_Record 作为 CE 

MINCE （最小ClockEvents）是用于选择最短时间的Raw_Record表的table_alias。

MAXCE （最大ClockEvents）是制表符用于选择最大时间的Raw_Record表的le_alias。

CE （ClockEvents）是主SELECT语句中使用的Raw_Record表的table_alias。

Good Day,

I am so confused which is the best approach to get employee's (first time in) & (last time out) each day when they have login to our biometrics clock system.

For instance if "Jorge" has a schedule of 8am - 5pm on March 9, 2013
and he logged many times and has a record attendance of: time in at 10:05, 10:10, 10:15 and Time out at : 5:00. 5:30, 5:45

System should only gets "Jorge's" attendance for today like:

Time in: March 9, 2013 - 10:05 am & Time out: March 9, 2013 - 05:45 pm

Emp_name Emp_time Emp_type
Jorge 3/9/2013 10:00 In
Jorge 3/9/2013 10:10 In
Jorge 3/9/2013 17:00 Out
Jorge 3/9/2013 17:30 Out
Mike 3/9/2013 17:40 Out
Jorge 3/9/2013 10:02 In
Jorge 3/9/2013 17:00 Out
Jorge 3/9/2013 17:00 Out
Jorge 3/9/2013 17:00 Out
Jorge 3/9/2013 10:14 In
Mike 3/9/2013 10:00 In
Mike 3/9/2013 10:10 In
Jorge 3/9/2013 17:00 Out
Mike 3/9/2013 17:30 Out
Jorge 3/9/2013 17:40 Out
Mike 3/9/2013 10:02 In
Mike 3/9/2013 17:00 Out
Mike 3/9/2013 17:00 Out
Mike 3/9/2013 17:00 Out
Jorge 3/9/2013 10:14 In


table name: Raw_Record
Col1 = Emp_name
Col2 = Emp_time
Col3 = Emp_type

Honestly i haven't tried any code because i am still thinking what is the best approach to get (first time in) & (last time out) for each employees attendance record on each day.

An idea from anyone would be very much helpful. thanks in advance.

Example for Microsoft SQL Server:

select distinct
CE.Emp_Name,
(select min(Emp_Time) from Raw_Record As MINCE where MINCE.Emp_Type ='In' and MINCE.Emp_Name=CE.Emp_Name AND CONVERT(varchar(8),MINCE.EMP_TIME,112)=CONVERT(varchar(8),CE.Emp_Time,112)) As MinTime,
(select max(Emp_Time) from Raw_Record As MAXCE where MAXCE.Emp_Type ='Out' and MAXCE.Emp_Name=CE.Emp_name AND CONVERT(varchar(8),MAXCE.EMP_TIME,112)=CONVERT(varchar(8),CE.Emp_Time,112)) As MaxTime
from Raw_Record As CE

MINCE (Minimum ClockEvents) is table_alias for Raw_Record table used in selection of Minimum Time.
MAXCE (Maximum ClockEvents) is table_alias for Raw_Record table used in selection of Maximum Time.
CE (ClockEvents) is table_alias for Raw_Record table used in main SELECT statement.