I already have the Query for Getting the Day shift but I'm Having a problem in the night shift, Which is this query.

SELECT user_id,
Min(verify_date) as First_IN,
Max(verify_date) as Last_Out
from tbl_attendance group by user_id,verify_date

But this query doesn't give the night shift so i tried another Query Which is:

SELECT user_id,verify_date,
Lead(verify_date,-1) OVER(PARTITION BY user_id order by verify_date desc)
from tbl_attendance

This Query Give's The past attendance but i don't know how do i get rid the extra attendance in my Biometrics (Or the Double tap in Biometrics) the Biometric is used as the door so i need to get the first in and last out.

And also I'm having a problem with my SQL Fiddle The Date Time is maybe broke it alway's give me this output 2019-10-17T07:55:00Z. See this DB Fiddle

Desired Output :

User_id     in_time             out_time
71      17/10/2019 7:55     null
13      16/10/2019 18:44    17/10/2019 7:05
8       16/10/2019 19:47    17/10/2019 7:05
70      16/10/2019 18:25    17/10/2019 7:01
31      16/10/2019 19:02    17/10/2019 6:34
66      17/10/2019 6:19     null
64      16/10/2019 11:49    16/10/2019 22:05
36      16/10/2019 7:42     16/10/2019 19:03
34      16/10/2019 6:26     16/10/2019 19:03
79      16/10/2019 9:25     16/10/2019 19:02
76      16/10/2019 7:02     16/10/2019 19:02
13      16/10/2019 7:05     16/10/2019 18:44
71      16/10/2019 7:02     null
  • I'm having a problem with my SQL Fiddle Use another fiddle. Lead(verify_date,-1) OVER(PARTITION BY user_id order by verify_desc) Either LEAD() or MySQL ver. 5.6. Not both. – Akina Oct 28 '19 at 5:29
  • @Akina why does the lead over query doesn't work in any version of dbfiddle in mysql ? – bpoom za Oct 28 '19 at 5:41
  • No-no-no. The desured answer must be FULL, not partial. If it is too huge then decrease the amount of source records. And post it as formatted text, not as a picture, please. – Akina Oct 28 '19 at 5:41
  • why does the lead over query doesn't work in any version of dbfiddle in mysql ? It works. When MySQL version is 8+. – Akina Oct 28 '19 at 5:42

For dayshift use

       DATE(verify_date) the_date,
       MIN(verify_date) OVER (PARTITION BY user_id, DATE(verify_date)) min_datetime,
       MAX(verify_date) OVER (PARTITION BY user_id, DATE(verify_date)) max_datetime
FROM tbl_attendance
ORDER BY user_id, the_date;

For nightshift use

WITH cte AS (SELECT user_id,
                    LEAD(verify_date) OVER (PARTITION BY user_id ORDER BY verify_date) lead_date
             FROM tbl_attendance)
FROM cte 
WHERE DATE(verify_date) != DATE(lead_date)
ORDER BY user_id, verify_date;


| improve this answer | |
  • This is query is good but I'm having an attendance as day shift even though I am night i.stack.imgur.com/lP02L.png – bpoom za Oct 28 '19 at 6:31
  • @bpoomza I do not see the criteria which allows to include the record into dayshift and exclude from nightshift, or backward. No enter/leave mark. If you guarantee no recortds is lost then you may enumerate records within partition by user_id and add one more condition - by this number parity. – Akina Oct 28 '19 at 6:49

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.