0

I have this attendance with multiple record. i can't get the attendance with too much record how do i remove the unusable attendance ?

id  user_id           verify_date    verify_type    verify_state    work_code
6     14        2019-11-11 20:05:00        1             1              0
-------I Want to Remove This Part ------------
7     14        2019-11-11 19:57:00        1             1              0
8     14        2019-11-11 16:09:00        1             0              0
9     14        2019-11-11 14:13:00        1             0              0
10    14        2019-11-11 13:30:00        1             0              0
11    14        2019-11-11 09:26:00        1             0              0
------------ To here and the id of 13 ---------
12    14        2019-11-11 07:20:00        1             0              0
13    14        2019-11-11 07:20:00        1             1              0
------- Here in user_id 15 i only want is the id of 24 and 30
23    15        2019-11-12 20:05:00        1             0              0
24    15        2019-11-12 19:57:00        1             0              0
25    15        2019-11-12 20:09:00        1             1              0
26    15        2019-11-12 20:13:00        1             1              0
27    15        2019-11-12 20:30:00        1             0              0
28    15        2019-11-13 06:26:00        1             0              0
29    15        2019-11-13 07:20:00        1             0              0
30    15        2019-11-13 07:20:00        1             1              0

Verify_State identify when 0 = Time in and 1 = Time out

What i have tried Queries are here in this Fiddle .

Problem : Day shift user_id of 14 on his attendance at 2019-11-11 he has so much attendance (Because they are using door access biometric for their attendance) the problem is how do i remove those not usable attendance and get the first in and last out. Also same with the Night shift user_id of 15

Exptected Output :

user_id  verify_date      verify_state  prev_state  next_state         next_date
14    2019-11-05 07:23:00       0           1           1          2019-11-05 20:00:00
14    2019-11-06 07:23:00       0           1           0          2019-11-07 20:09:00
14    2019-11-08 07:18:00       0           1           1          2019-11-08 17:38:00
14    2019-11-11 07:20:00       0           1           1          2019-11-11 20:05:00
15    2019-11-12 19:57:00       0           1           0          2019-11-13 07:20:00

Your Answer

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

Browse other questions tagged or ask your own question.