1

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.

1

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

| improve this answer | |
  • there's no output if it has this kind of multiple record. fiddle – bpoom za Nov 12 '19 at 5:21
  • @bpoomza You must describe the algo for the result obtaining carefully. For example, now I cannot understand what record of 2 is starting one and what record of 3 is finishing one. – Akina Nov 12 '19 at 5:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.