Export Attendance SQL query

Question

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.

Table:

Date	circuit_label	Fisr_name	Last_name	Logical_code
2023-10-04 07:45:24	Access 2 AC00GF-07-796	Alh	Yousef	24
2023-10-04 08:11:38	Access 2 AC00GF-07-796	Mon	Ivan	01
2023-10-04 08:16:02	Access 2 AC00GF-07-796	Al	Omar	66
2023-10-04 08:27:20	Access 2 AC00GF-07-796	Sar	Mahmod	121
2023-10-04 10:42:22	Access 2 AC00GF-07-796	Imr	M	02
2023-10-04 11:18:48	Access 1 AC00GF-07-796	Imr	M	02
2023-10-04 11:33:40	Access 2 AC00GF-07-796	Imr	M	02
2023-10-04 15:45:24	Access 2 AC00GF-07-796	Alh	Yousef	24

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.

Want to export:

In_time	Out_time	Fisr_name	Last_name	Logical_code
2023-10-04 07:45:24	2023-10-04 15:45:24	Alh	Yousef	24
2023-10-04 08:11:38	the employee hasn't left yet	Mon	Ivan	01
2023-10-04 08:16:02	the employee hasn't left yet	Al	Omar	66
2023-10-04 08:27:20	the employee hasn't left yet	Sar	Mahmod	121
2023-10-04 10:42:22	2023-10-04 11:33:40	Imr	M	02

Answer 1

You will just need to group by Fisr_name, Last_name, Logical_code the results of the query that filtered by the date interval of your preference and then find the min and max Date for your groups:

select min(Date) as In_time, max(Date) as Out_time, Fisr_name, Last_name, Logical_code
from yourtable
where Date >= '2023-10-04 00:00:00' and 
      Date < '2023-10-05 00:00:00'
group by Fisr_name, Last_name, Logical_code