How to get First in and Last out with multiple records? I have this records and it is attendance of each employee. I'd like to get their attendance, what i have tried Query is like this which is getting their attendance but if it has a multiple record it will get the latest attendance.
Verify_States stands for 0 = Time in and 1 = Time out
This is the Query what i have tried :
select * from 
(select A.*, 

LEAD(A.Verify_State,1) OVER (PARTITION BY A.user_id 
ORDER BY A.user_id, A.verify_date) as Nxt_row_State, 

LEAD(A.verify_date,1) OVER (PARTITION BY A.user_id 
ORDER BY A.user_id, A.verify_date) as TO_Date 
from tbl_excel_attendance as A ) as tbl_DTR_Mapped 
where tbl_DTR_Mapped.verify_state = 0 and tbl_DTR_Mapped.Nxt_row_State = 1

EDIT : I Also Tried This Query
    Select * from (
SELECT DISTINCT
       user_id,
       verify_state,
       DATE(verify_date) the_date,
       MIN(verify_date) OVER (PARTITION BY user_id, DATE(verify_date)) min_datetime,
       LEAD(Verify_State,1) OVER (PARTITION BY user_id 
ORDER BY user_id, verify_date) as Nxt_row_State, 
       MAX(verify_date) OVER (PARTITION BY user_id, DATE(verify_date)) max_datetime
FROM tbl_excel_attendance) as q
where q.verify_state = 0 and q.nxt_row_state = 1
ORDER BY q.user_id, q.the_date;

Sample SQL Fiddle
Desired Output :
id  user_id      verify_date    verify_state  Nxt_row_State    TO_Date
6      16    2019-11-06 07:00:00    0            1       2019-11-06 19:45:00
3      16    2019-11-07 07:35:00    0            1       2019-11-07 18:05:00
13     19    2019-11-05 06:57:00    0            1       2019-11-05 18:28:00
10     19    2019-11-07 06:42:00    0            1       2019-11-07 18:04:00
15     39    2019-11-07 07:39:00    0            1       2019-11-07 19:09:00
20     40    2019-11-06 20:39:00    0            1       2019-11-07 06:39:00

Which verify_date is time in and TO_Date is time out
The Problem is the night shift. I can't get the attendance of night shift with multiple rows.
Look for

with 
cte1 as ( select *, 
                 coalesce(lag(verify_state) over (partition by user_id order by verify_date asc),1) prev_state,
                 coalesce(lead(verify_state) over (partition by user_id order by verify_date asc),0) next_state
          from tbl_excel_attendance
),
cte2 as ( select *, 
                 lead(verify_date) over (partition by user_id order by verify_date asc) next_date
          from cte1 
          where (prev_state,verify_state,next_state) in ( (1,0,0), (1,0,1), (0,1,0) )
)
select * 
from cte2 
where verify_state = 0
  and next_date is not null
order by 2,3


fiddle