I’m storing punch-in and punch-out data in a table that looks like so:
| id | empid | punchtime |
|---|---|---|
| 74 | 4 | 2021-08-04 17:11:54 |
| 171 | 4 | 2021-08-06 13:47:45 |
| 202 | 4 | 2021-08-09 10:14:01 |
| 271 | 4 | 2021-08-09 18:20:01 |
| 308 | 4 | 2021-08-10 11:14:54 |
| 343 | 4 | 2021-08-10 14:46:21 |
| 349 | 4 | 2021-08-10 15:22:10 |
| 380 | 4 | 2021-08-10 18:10:58 |
| 406 | 4 | 2021-08-11 10:13:48 |
I want to check for punch in vs. punch out per day. So each odd n-th punch is considered a punch in, and each even n-th punch is considered a punch out. I also have a table with employee information, i.e. employee ID and name.
Here’s my SQL query:
WITH PunchDataPlus AS ( SELECT pd.*, ei.Name, ROW_NUMBER() OVER(PARTITION BY EmpID, datediff(PunchTime, '2021-08-11')=0 ORDER BY PunchTime) AS RN FROM PunchData pd LEFT JOIN EmployeeInfo ei ON pd.EmpID=ei.EmpID ), FinalPunchData AS ( SELECT PunchDataPlus.*, CASE WHEN RN % 2 =1 THEN 'IN' ELSE 'OUT' END AS inOutCol FROM PunchDataPlus ) SELECT * FROM FinalPunchData WHERE EmpID=4 ORDER BY PunchTime DESC;
The problem with this query is that the datediff can only compare against one day at a time. How do I compare against each day so that each day has its own in/out values?
The result of the SQL query:
| id | EmpID | PunchTime | Name | RN | inOutCol |
|---|---|---|---|---|---|
| 406 | 4 | 2021-08-11 10:13:48 | redacted | 1 | IN |
| 380 | 4 | 2021-08-10 18:10:58 | redacted | 8 | OUT |
| 349 | 4 | 2021-08-10 15:22:10 | redacted | 7 | IN |
| 343 | 4 | 2021-08-10 14:46:21 | redacted | 6 | OUT |
| 308 | 4 | 2021-08-10 11:14:54 | redacted | 5 | IN |
| 271 | 4 | 2021-08-09 18:20:01 | redacted | 4 | OUT |
| 202 | 4 | 2021-08-09 10:14:01 | redacted | 3 | IN |
| 171 | 4 | 2021-08-06 13:47:45 | redacted | 2 | OUT |
| 74 | 4 | 2021-08-04 17:11:54 | redacted | 1 | IN |
The result I’m looking for:
| id | EmpID | PunchTime | Name | RN | inOutCol |
|---|---|---|---|---|---|
| 406 | 4 | 2021-08-11 10:13:48 | redacted | 1 | IN |
| 380 | 4 | 2021-08-10 18:10:58 | redacted | 4 | OUT |
| 349 | 4 | 2021-08-10 15:22:10 | redacted | 3 | IN |
| 343 | 4 | 2021-08-10 14:46:21 | redacted | 2 | OUT |
| 308 | 4 | 2021-08-10 11:14:54 | redacted | 1 | IN |
| 271 | 4 | 2021-08-09 18:20:01 | redacted | 2 | OUT |
| 202 | 4 | 2021-08-09 10:14:01 | redacted | 1 | IN |
| 171 | 4 | 2021-08-06 13:47:45 | redacted | 1 | IN |
| 74 | 4 | 2021-08-04 17:11:54 | redacted | 1 | IN |
Advertisement
Answer
You may try changing datediff(PunchTime, '2021-08-11')=0 to DATE(PunchTime). This will extract just the date portion of your datetime value and thus as desired partition your data by each day.
Let me know if this works for you.