Try this one

declare @emp table(EMPNo varchar(10),fullnam varchar(20))

declare @tfile table(EMPNo varchar(10),CDate datetime,Ctime datetime)

insert into @emp

select * from

(

Values

('0001','xyz1'),

('0002','xyz2'),

('0003','xyz3'),

('0004','xyz4'),

('0005','xyz5')

)a (no,name)

insert into @tfile

select * from

(

Values

('0001','10/29/2012','2012-10-29 07:52:00.000'),

('0001','10/29/2012','2012-10-29 19:00:00.000'),

('0002','10/29/2012','2012-10-29 07:40:00.000'),

('0002','10/29/2012','2012-10-29 19:32:00.000'),

('0005','10/29/2012','2012-10-29 07:58:00.000'),

('0005','10/29/2012','2012-10-29 18:35:00.000')

)a (no,cdate,ctime)

-----------------------------------------------------------------------------------------

select a.EMPNo,cdate,MIN(Ctime) AS timeIn ,MAX(Ctime) AS timeOut

from @emp a

LEFT join @tfile b on a.EMPNo=b.EMPNo AND CDate='10/29/2012'

GROUP BY a.EMPNo,cdate

ORDER BY A.EMPNo