I need to find first in and last out time for all employees from a table. I have tried the following query.
x
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
Advertisement
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