Fetch data with open status but no closed status in mysql

Viewed 28

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;
0 Answers
Related