CODEWITHSUNDEEP
sqlsql-server
Run
Run

Reputation: 45

How to create all Time Attendance records for Each User In SQL Server

I have a question - I have 2 tables like this :

tblattLogs:

| user_id | attendantLogs    |
+---------+------------------+
|   01    | 2017-10-31 08:00 |
|   01    | 2017-10-31 12:00 |
|   01    | 2017-10-31 17:05 |
|   01    | 2017-10-31 17:10 |
|   02    | 2017-10-31 08:10 |
|   02    | 2017-10-31 11:00 |
|   02    | 2017-10-31 17:01 |
|   02    | 2017-10-31 17:05 |
......

tblusers:

| id | name | otherstuff |..
+----+------+------------+
| 01 | Joe  | otherstuff |
| 02 | Jean | otherstuff |
...

and I want the following as a result:

| id | name | date       | CheckIn | CheckOut |
+----+------+------------+---------+----------+
| 01 | Joe  | 2017-10-31 | 08:00   | 17:10    |
| 02 | Jean | 2017-10-31 | 08:10   | 17:05    |
...

And my query looks like:

SELECT DISTINCT
    t.[user_id],
    MIN(t.attendantLogs) OVER (PARTITION BY [user_id]) AS CheckIn,
    MAX(t.attendantLogs) OVER (PARTITION BY [user_id]) AS CheckOut,
    n.name AS NAME 
FROM
    tblattLogs t, tblusers as n 
WHERE
    t.user_id = n.id

My question is: how do I create a query that shows me correct result as I want? and I know my query is wrong. I'm literally beginner in SQL. So, please correct me if I am wrong, and please help me to resolve this.

Upvotes: 3

Views: 992

Answers (3)

TheGameiswar
TheGameiswar

Reputation: 28930

One way would be to use group by.DEMO based on data by Stuart

;with cte
as
(
select user_id,
cast(attendant_logs as date) as dt,
min(cast(attendant_logs as time)) as checkin,
max(cast(attendant_logs as time)) as checkout
from
#tblattLogs
group by user_id,
cast(attendant_logs as date) 

)
select u.id,u.name,
c.*
from
#tblusers u
join
cte c
on c.user_id=u.id

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Here is single SQL Query which could help u to achieve the above result :

SELECT U.ID,
       U.name,
       CONVERT(DATE, MIN(L.attendantLogs)) [Date],
       CONVERT(VARCHAR(8), MIN(L.attendantLogs), 108) [CheckIn],
       CONVERT(VARCHAR(8), MAX(L.attendantLogs), 108) [CheckOut]
FROM tblattLogs L
     INNER JOIN tblusers U ON U.id = L.USER_ID
GROUP BY U.id,
         U.name;

Result :

ID  name    Date        CheckIn     CheckOut
01  Joe     2017-10-31  08:00:00    17:10:00
02  Jean    2017-10-31  08:10:00    17:05:00

Upvotes: 0

StuartLC
StuartLC

Reputation: 107347

The trick here is to build up a definitive list of all dates as a derived table or cte.

You can then CROSS JOIN the users and dates tables, and then JOIN back to the Attendance data, grouping and using the MIN / MAX aggregates as before.

The LEFT JOIN ensures that there will always be a record for every employee for any day on at least one employee went to work. The attendance will be NULL if that employee didn't check in / out at all.

WITH cteUniqueDates AS
(
  SELECT DISTINCT CAST(attendantLogs AS DATE) as AttendanceDate
  FROM tblattLogs
)
SELECT u.id, u.name, d.AttendanceDate, min(attendantLogs) AS CheckIn, 
       max(attendantLogs) AS CheckOut
FROM tblusers u
  CROSS JOIN cteUniqueDates d
  LEFT OUTER JOIN tblattLogs t
    ON u.Id = t.user_id AND CAST(attendantLogs AS DATE) = d.AttendanceDate
GROUP BY u.id, u.name, d.AttendanceDate
ORDER BY d.AttendenceDate, u.id;

SqlFiddle Here

One caveat - this will only work if the employees check in and checkout on the same day. If the employees work overnight, things become more complicated.

Upvotes: 1

Related Questions