2

How do i delete multiple records of attendance ? I have tried Several Queries to delete the Unwated data. also verify state identify

 0 = Time In and 1 = Time Out

This is the sample Fiddle

Expected Output :

user_id        verify_date           verify_state   prev_state  next_state      TO_Date
14           2019-11-15 07:29:00         0               1         1       2019-11-15 19:12:00
15           2019-11-15 22:12:00         0               1         1       2019-11-16 06:29:00

Sample Query i have tried

delete from tbl_excel_attendance where CONCAT(User_id,verify_date) in (select  CONCAT(User_id,verify_date)  from
(select
A.*,
LAG(A.Verify_State) OVER (PARTITION BY A.User_id ORDER BY A.User_id, A.verify_date) as Prv_row_State,
LEAD(A.Verify_State) OVER (PARTITION BY A.User_id ORDER BY A.User_id, A.verify_date) as Nxt_row_State,
LEAD(A.verify_date) OVER (PARTITION BY A.User_id ORDER BY A.User_id, A.verify_date)  as TO_Date

from tbl_excel_attendance as A ) as X
where
(X.Prv_row_State = 1 and X.verify_state = 1 and  X.Nxt_row_State = 0) OR
(X.Prv_row_State = 1 and X.verify_state = 1 and  X.Nxt_row_State = 1 and TIMESTAMPDIFF(Hour,X.verify_date,X.TO_Date)< 12.01) OR
(X.Prv_row_State = 0 and X.verify_state = 1 and  X.Nxt_row_State = 1 and TIMESTAMPDIFF(Hour,X.verify_date,X.TO_Date)< 12.01) OR
(X.Prv_row_State = 0 and X.verify_state = 0 and  X.Nxt_row_State = 1) OR
(X.Prv_row_State = 0 and X.verify_state = 0 and  X.Nxt_row_State = 0) OR
(X.Prv_row_State is null and X.verify_state = 1 and  X.Nxt_row_State = 1))

Which deletion of attendance base on my analysis

EDIT : Their Biometric is Door Access so it means if they need to go out they need to tap the biometric.

User id of 15 is a night shift.

Sample Image

Bap mop
  • 320
  • 2
  • 15
  • How do you know User id of 15 is a night shift? When does night shift start? You haven't stated what it is you are trying to achieve - looks like based on earliest and latest verify date for shift? – P.Salmon Nov 20 '19 at 08:35
  • I'm basing it on the First in and last out in verify date and i don't based it in Shifts. – Bap mop Nov 20 '19 at 08:56
  • Can you explain the logic for the different dates for user 15 ? I am unclear what should happen if comes back on shift '2019-11-16 22:12' or if changes shift pattern. – P.Salmon Nov 20 '19 at 09:06
  • The verify date ``'2019-11-16 22:12'`` Is the First Time in on his attendance as you can see his verify_state is 0 = Time In and get the last verify_state = 1 which is ``2019-11-16 06:29`` = Time out – Bap mop Nov 20 '19 at 09:33

0 Answers0