I have a logs table which consists of data in which user has opened (1) or closed (2) status. My problem is I need to get the user with registration_id that has open status but no close status.
Logs Table
id | registration_id | user_id | status | created_at
1 | 1 | 1 | 1 | 2021-02-22 8:00:00
2 | 1 | 1 | 2 | 2021-02-22 8:30:00
3 | 2 | 1 | 1 | 2021-02-22 8:30:00
4 | 2 | 1 | 2 | 2021-02-22 9:00:00
5 | 3 | 1 | 1 | 2021-02-22 9:00:00
6 | 4 | 2 | 1 | 2021-02-22 8:00:00
7 | 4 | 2 | 2 | 2021-02-22 8:30:00
Expected Output
id | registration_id | user_id | status | created_at
5 | 3 | 1 | 1 | 2021-02-22 9:00:00
Since the registration_id = 3 with user_id = 1 don't have a closed status. Also, there's a lot of logs between open and closed, I just simplified it in my question so, if you're planning to just count the registration_id if it's equals to 1. It doesn't work.
What I've tried is subtracting the closed created_at - open created_at and if the total is less than or equal 0, it doesn't have a close status but I know there's a better way to get what I wanted because my current query is very slow.
SELECT
user_id,
registration_id,
date,
SUM(timestampdiff(minute, openTime, closedTime)) AS total
FROM (
SELECT
user_id,
date(created_at) as `date`,
created_at as openTime,
registration_id,
coalesce(
(
SELECT created_at FROM logs t2
WHERE t1.registration_id = t2.registration_id
AND t1.created_at < t2.created_at
AND t1.user_id = t2.user_id
AND status = 2
ORDER BY t1.created_at
LIMIT 1
),
date_add(t1.created_at, interval -1 minute)
) AS closedTime
FROM logs t1
WHERE status = 1
) a
GROUP BY a.user_id, registration_id
HAVING total <= 0;