CODEWITHSUNDEEP
sqlcount
Lucca F
Lucca F

Reputation: 33

How to count the last records of a given status?

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.

Upvotes: 2

Views: 81

Answers (2)

PCastedo
PCastedo

Reputation: 74

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520918

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;

Upvotes: 1

Related Questions