WOW!!!! Your second post and you nailed the ddl and sample data. Excellent job!!! This takes many people dozens of posts, if they ever actually do it right. The only minor change was I had to remove the ID from the column list on the insert. Having ddl and sample data makes helping so much easier. Thank you for that.

It would be nice if I knew what you expected as output based on your sample data but in this case the result set is small enough it isn't too bad. I think this should get you what you are looking for.

select i.EmpID, i.Htime as InTime, o.Htime as OutTime, DATEDIFF(minute, i.Htime, o.Htime) as MinutesSpent

from tblEMPLog i

outer apply

(

select top 1 *

from tblEMPLog OutPunches

where OutPunches.EmpID = i.EmpID

and Status = 'Out'

and OutPunches.Htime > i.Htime

order by Htime

) o

where i.Status = 'In'

This will return rows when there is no matching 'Out' punch. If you want to eliminate those you can just change the outer apply to a cross apply.