Salis Younsi Salis Younsi - 3 months ago 30
SQL Question

Attendance Time in and Time out Sql Query

I would like get help in the following Data , we have hand punch machines which saves exit and entry log , each exit and entry log stores a different row for an employee id

EMP ID | EntryTime | ExitTime
11769 2015-02-01 08:00:00 NULL
11769 NULL 2015-02-01 13:00:00


Basically every two entry for in and out.

I would like to get the time in and time out for a single day . As you can see this is the time in and time out for a day , but it stores two records .
So in this regard I am quite confused how to tackle this. also would like to calculate the hours worked

A help would be very much appreciated

Answer

use a subquery in such cases.

 SELECT DISTINCT at1.empId ,at1.EntryTime ,
        (SELECT    MIN(a2.ExitTime)
          FROM      attendance at2
          WHERE     at1.empId = at2.empId
                    AND  at2.ExitTime >at1.EntryTime ) ExitTime
FROM    attendance at1
WHERE   at1.EntryTime IS NOT NULL