I need to find first in and last out time for all employees from a table. I have tried the following query. This query return the records for a particular employee. But I have to find the data for all employees. Sample Table data: Answer you can use this.

Query to fetch in-out time for all employees from one table

I need to find first in and last out time for all employees from a table. I have tried the following query.

select 
  attDate, 
  min(cast(attTime as time)) AS 'inTime',  
  max(cast(attTime as time)) AS 'outTime' 
from EmployeePunch 
where attDate between @minAttDate 
     and @maxAttDate and accessCardNum = 121 
group by attDate 
ORDER BY attDate asc

This query return the records for a particular employee. But I have to find the data for all employees.

Sample Table data:

Sno.attDate     attTime             accessCardNum
1   2019-09-16  11:23:00.0000000    107
1   2019-10-12  12:30:00.0000000    107
1   2019-10-04  11:53:00.0000000    108
1   2019-09-04  15:09:00.0000000    107
1   2019-09-21  09:10:00.0000000    107
1   2019-10-10  19:56:00.0000000    107
1   2019-10-04  11:09:00.0000000    105
1   2019-10-15  15:02:00.0000000    107
1   2019-09-18  16:33:00.0000000    107
1   2019-10-18  18:23:00.0000000    107
1   2019-10-02  18:31:00.0000000    108
1   2019-10-01  21:04:00.0000000    107
1   2019-08-20  20:52:00.0000000    106
1   2019-09-03  11:06:00.0000000    107
1   2019-09-19  17:12:00.0000000    105
1   2019-08-19  20:37:00.0000000    107
1   2019-09-10  18:16:00.0000000    103
1   2019-09-12  14:15:00.0000000    107

Answer

you can use this.

SELECT 
    accessCardNum,
    attDate, 
    MIN(CAST(attTime AS TIME)) AS 'inTime', 
    MAX(CAST(attTime AS TIME)) AS 'outTime' 
FROM EmployeePunch 
WHERE attDate BETWEEN @minAttDate AND @maxAttDate     
GROUP BY accessCardNum, attDate 
ORDER BY accessCardNum, attDate

Advertisement

Answer