Get one Clock In and one clock out time from a record employee table

In the attendance system, many users when uses the attendance device can commit mistakes when they imprint two times or more ( for the clockIn ) and two time or more ( for the clockOut) . So what I have is a table that get all records ( ClockIn and ClockOut ) for all users " duplicated " .

I'm searching for a method to get only the max of the clockout and the min of the clockIn.

I saw a solution for this question on stackoverflow :

Get Clock In and clock out time from multiple clock in or multiple clock out in SQL Server

I have tested it , it gives the result expected but not in all case.

1/ Table schema and select query that I have created:

In my case, I have a table that contains employee_records and I would like to get only one ClockIn and only ClockOut in the same date .

employeerecordId        AddAt           logtype

What I need exactly is getting all clockIn and clockOut between two dates ( duration ) ( only one clock and one clockOut for the same day)

how can I do that?

select t.employeeId,MIN(cast(clockIn as time(7))) ClockIn, max(cast(ClockOut as time(7))) clockout 
from (select r.employeeId, MIN(cast(r.AddAt as time(7))) clockIn,MAX(cast(r.AddAt as time(7))) clockout , logtype
from emplyee_recoards r 
group by r.employeeId,logtype
)t 
where CAST(a as date) between @datefrom and @dateto 
group by employeeId

2/ Sample table data to test the solution proposed here

Create the table and insert data on it:

Create table #MyTempTable (employeeId int , addat Datetime,LogType int)
    Insert into #MyTempTable values (5005,'2019-05-20 21:35:48.490',1)
    Insert into #MyTempTable values (5005,'2019-05-20 22:25:00.000',1)
    Insert into #MyTempTable values (5005,'2019-05-20 06:48:00.000',0)
    Insert into #MyTempTable values (5005,'2019-05-20 07:01:15.383',0)
    Insert into #MyTempTable values (5005,'2019-05-25 08:01:15.383',0)
    Insert into #MyTempTable values (5005,'2019-05-25 09:01:15.383',0)
    Insert into #MyTempTable values (5005,'2019-05-25 22:01:15.383',1)
    Insert into #MyTempTable values (5005,'2019-05-25 20:01:15.383',1)
    
    Insert into #MyTempTable values (5006,'2019-05-20 21:25:25.470',1)
    Insert into #MyTempTable values (5006,'2019-05-20 23:48:29.568',1)
    Insert into #MyTempTable values (5006,'2019-05-20 08:48:29.568',0)
    Insert into #MyTempTable values (5006,'2019-05-20 09:38:29.568',0)
    Insert into #MyTempTable values (5005,'2019-05-25 08:01:15.383',0)
    Insert into #MyTempTable values (5005,'2019-05-25 09:01:15.383',0)
    Insert into #MyTempTable values (5005,'2019-05-25 22:01:15.383',1)
    Insert into #MyTempTable values (5005,'2019-05-25 20:01:15.383',1)



select employeeId,min(Date) Date ,min(ClockIn) ClockIn ,Max(ClockOut) ClockOut 
from (
    select employeeId, Min(convert(date,addat)) Date, 
    Min(addat) ClockIn , Max(addat) ClockOut,LogType
    from #MyTempTable
    Group by employeeId,LogType
    having count(convert(date,addat)) > 1
    )t
Group by employeeId

Drop table #MyTempTable

will return:

The expected result:

EmployeeId      AddAt            ClockIn                    ClockOut
5005               2019-05-20     2019-05-20 06:48:00.000     2019-05-20 22:25:00.000
5005               2019-05-25     2019-05-25 08:01:15.383     2019-05-25 22:01:15.383
5006               2019-05-20     2019-05-20 08:48:29.568     2019-05-20 23:48:29.568
5006               2019-05-25     2019-05-25 08:01:15.383     2019-05-25 22:01:15.383

Looks like you want to group by the employee and date, then use conditional aggregation to split out the clock-ins and -outs.

select
    employeeId,
    convert(date,addat) Date, 
    Min(CASE WHEN LogType = 0 THEN addat END) ClockIn,
    Max(CASE WHEN LogType = 1 THEN addat END) ClockOut
from #MyTempTable
Group by employeeId, convert(date,addat)