How to count the last records of a given status?

Question

I need to count the last few months a member has had a D status.

For example, I have the table below, where I have the months from February to August for 2 members.

year_month	member_id	status
2020_02	1010	D
2020_03	1010	D
2020_04	1010	D
2020_05	1010	A
2020_06	1010	A
2020_07	1010	D
2020_08	1010	D
2020_02	1030	A
2020_03	1030	A
2020_04	1030	A
2020_05	1030	D
2020_06	1030	A
2020_07	1030	A
2020_08	1030	D

I need to count the number of months a member has been in D status in a row. In this example the expected result would be:

member_id	count status D
1010	2
1030	1

For member 1010 I need to count July and August, because in June he had A status.

Can anyone help me, please?

I'm a beginner and I have no idea how I can do this.

user_1863229 · Answer

We can try first filtering for each member to only latest D records. Then, aggregate by members and find the counts.

SELECT member_id, COUNT(*) AS count_status_D
FROM
(
    SELECT member_id
    FROM yourTable t1
    WHERE status = 'D' AND
          NOT EXISTS (SELECT 1
                      FROM yourTable t2
                      WHERE t2.member_id = t1.member_id AND
                            t2.year_month > t1.year_month AND
                            t2.status <> 'D')
) t
GROUP BY member_id;

user_18610162 · Answer

With SQL you could use for example:

SELECT COUNT(DISTINCT member_ID) AS member_ID FROM Table WHERE Status = D;

For more info: http://www-db.deis.unibo.it/courses/TW/DOCS/w3schools/sql/sql_func_count.asp.html

How to count the last records of a given status?

2 Answers