Query filter by status on most recent date-CodePudding

I'm trying to query my data based on date and status, by ID.

For a given date query, it should return the IDs whose most recent status (up to and including the query date) is "Active". In other words, it should return data which reflects only the IDs that were Active at the query date.

  A         B         C
 Date   | Status   | ID 
--------|----------|-------
4/1/22  | Inactive | Bob
5/1/22  | Active   | Sally
5/1/22  | Active   | Bob
6/1/22  | Active   | Jim
7/5/22  | Inactive | Sally
10/1/22 | Active   | Sally
10/1/22 | Inactive | Bob

For example, if I query 8/1/22, it should return the following:

  A         B         C
 Date   | Status   | ID 
--------|----------|-------
5/1/22  | Active   | Bob
6/1/22  | Active   | Jim

I've tried something similar to

CodePudding user response：

You can do the whole thing within QUERY - no need for any SORT/FILTER operations as well:

=query(A:C,"where A<= date '"&text(datevalue("08/01/2022"),"yyyy-mm-dd")&"' and B='Active'",1)

N.B. QUERY requires date arguments to be in ISO format.