counting records from a grouped query with additional criteria

Question

I have a table of reports that has these relevant fields:

user_id (int 11)
submitted_date (datetime)
approved_flag (shortint 1)

There are multiple rows per user_id, some with approved_flag = 0 and some with approved_flag = 1 and each with a unique submitted_date.

I need to get a count of the approved and unapproved reports. But I only want to count the most recent submitted report per user (most recent submitted_date), regardless of approved_flag.

So, if there was 4 records like this:

    user_id, submitted_date, approved_flag
    1, 2009-04-01 01:00, 1
    1, 2009-04-01 02:00, 0
    1, 2009-04-01 03:00, 1 (using this record)
    2, 2009-04-02 01:00, 1 (using this record)

the count would be 2 approved and no unapproved, and if we had

    user_id, submitted_date, approved_flag
    1, 2009-04-01 01:00, 1
    1, 2009-04-01 02:00, 0
    1, 2009-04-01 03:00, 0 (using this record)
    2, 2009-04-02 01:00, 0
    2, 2009-04-02 02:00, 1 (using this record)

the count would be 1 approved (user id 2) and 1 unapproved (user id 1).

To summarize one more time because it is not easy to describe: I want the total number of unique users in the table (regardless of # rows per user) and I want it broken down in to 'approved' and 'not approved' based on the approved_flag of the most recent report per user.

Any help would be greatly appreciated, thanks!

Answer 1

SELECT user_id, approved_flag, COUNT(*)
FROM reports 
WHERE NOW() - submitted_date < 'whatever'
GROUP BY user_id, approved_flag

Hope it works..

Answer 2

SELECT approved_flag, COUNT(*)
FROM 
  Table t
  INNER JOIN (
    SELECT user_id, submitted_date = MAX(submitted_date)
    FROM Table
    GROUP BY user_id
  ) latest ON latest.user_id = t.user_id
              AND latest.submitted_date = t.submitted_date
GROUP BY approved_flag

Answer 3

Can think of two ways to do this. The first is perhaps the most readable, search for the last submitted_date per user, and then display the approval with that timestamp:

select a.user_id, a.submitted_date, a.approved_flag
from approvals a
inner join (
    select user_id, maxdt = max(submitted_date)
    from approvals
    group by user_id
) latest on latest.user_id = a.user_id 
    and latest.maxdt = a.submitted_date

The second is to join the table on it's future rows for the same user, and specify in the where clause that these don't exist. This gives you the latest entry for each user:

select cur.user_id, cur.submitted_date, cur.approved_flag
from approvals cur
left join approvals next
    on next.user_id = cur.user_id
    and next.submitted_date > cur.submitted_date
where next.user_id is null