MySQL 8.0 – How to Get First In and Last Out with Multiple Records

MySQLmysql-8.0

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.

Best Answer

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

Related Solutions

Mysql – Query for data that is not there

Why are you determining a member's absence through another member's presence?

I guess there must be an events table in your schema. And I would expect it to have a column called something like event_date. It's the event_date column that you should use to determine which events a member could visit (members.registration_date < events.event_date).

After getting the list of members and their respective valid events, you can anti-join it to the attendance table to see which member missed which (or how many) events.

Here's an example query illustrating what I just said. It implements the anti-join using LEFT JOIN + WHERE IS NULL check:

SELECT
  m.member_id,
  e.event_id
FROM       members    m
INNER JOIN events     e ON m.registration_date < e.event_date
LEFT  JOIN attendance a ON m.member_id = a.member_id
                       AND e.event_id  = a.event_id
WHERE a.member_id IS NULL
  AND ... /* some other conditions to filter members and/or events, if necessary */

Or here's another example showing how you could count total events available to a member and those actually attended by him/her:

SELECT
  m.member_id,
  COUNT(e.event_id) AS total_events,
  COUNT(a.event_id) AS attended_events,
FROM       members    m
INNER JOIN events     e ON m.registration_date < e.event_date
LEFT  JOIN attendance a ON m.member_id = a.member_id
                       AND e.event_id  = a.event_id
WHERE /* some conditions as necessary */

Note that in the last example there's no IS NULL check. It's because that query needs to get all the events per member. Missed events are just not counted by the COUNT() function (because the corresponding a.event_id value contains NULL in those cases).

UPDATE

Since, as per your comment, the events are recurring (and thus have no fixed dates), I would suggest adding and using an event_calendar table, populated beforehand either manually or programmatically. Naturally, the events table in the above examples would be replaced with event_calendar.

Alternatively, if maintaining an event calendar is not an option, you could replace events in your queries with

(
  SELECT DISTINCT
    event_id,
    attendance_date AS event_date
    /* or, if they are not purely dates:
    CAST(attendance_date AS date) AS event_date
    */
  FROM attendance
) e

(basically, @ypercube's suggestion with dates thrown in).

MySQL Subquery – How to Solve Correlated Subquery Problems

Your query should be like:

SELECT FIRST_NAME, MIN_EMP_SALARY FROM
(
    SELECT  FIRST_NAME , (SELECT MIN(SALARY) 
                         FROM EMPLOYEES EMP
                         WHERE E.EMPLOYEE_ID = EMP.EMPLOYEE_ID
                         GROUP BY EMPLOYEE_ID) AS MIN_EMP_SALARY
    FROM  EMPLOYEES E
    GROUP BY FIRST_NAME , EMPLOYEE_ID
) OUTER_TABLE WHERE MIN_EMP_SALARY >= 6000
ORDER BY MIN_EMP_SALARY DESC

Best Answer

Related Solutions

Mysql – Query for data that is not there

MySQL Subquery – How to Solve Correlated Subquery Problems

Related Question