Google Sheets: selecting cell values based on another cell MAX values

Question

I'm trying to make a database of students using Google Sheets. It contains info about students, groups and orders; orders can change students membership in groups (taken in a group, moved up to a new group, graduated, on leave, sent down). Here are sample database sheets and here is a detailed description of my DB structure (the sheet report_Groups is slightly changed, its previous variant, described on the link, is now named old_report_Groups).

I need a query that would select a list of present members of given group on the given date. That means that for each student I have to select the name, the latter status before given date and corresponding group. And from this result select student names, where statuses are "Taken in" or "Moved Up" and group is the same as given one.

The problem is to select the latter status. It should be MAX(status), whose "since" date ≤ given date, but there's a well-known problem of selecting more than one field together with aggregate function. Here is a question which is very close to, but query from its "best" answer gives me error "QUERY:NO_COLUMN". I've even copied the sheet Raw from there and tried to perform proposed query (with the onliest modification — replacing commas with semicolons according to my locale restrictions) on the data it was reported to work on — same error (check Raw and report_Raw sheets in my DB). Other variant (via MMULT and TRANSPOSE) works, but it's perfomance is very poor.

What can you suggest me? Thanks in advance.

Update: I've found the solution with an issue (described in my answer). To solve the issue I need to know an answer for a different question.

Answer 1

Here's the solution (with an issue described below).

A. Orders_Students is filtered for selecting rows, having "since" cell value?≤ given date (report_Groups!A2):

=QUERY(Orders_Students!B:E;"select E, B, C, D where E <= date '" & TEXT(report_Groups!A2;"yyyy-MM-dd") & "'";1)

This interim result is stored at the inner_report_Groups tab (it will be referenced few times in the next query).

B. inner_report_Groups is filtered for selecting MAX("since") values and corresponding row cell values for each student:

ARRAYFORMULA(VLOOKUP(QUERY({ROW(inner_report_Groups!A$2:A)SORT(inner_report_Groups!A$2:D)};"select max(Col1) group by Col3 label max(Col1)''";0);{ROW(inner_report_Groups!A$2:A)SORT(inner_report_Groups!A$2:D)};{345};0)

The formula above is used as inner query in report_Groups!D2 (also in D3, D4—with appropriate indeces).

C. The second query result is filtered to get students whose status is either "Taken in" or "Moved Up" and corresponding group is equal to the given group (report_Groups!B2 (also in B3, B4—with appropriate indeces)):

=TRANSPOSE(IFERROR(QUERY(<here is the formula from step B>);"select Col1 where Col3 = '" & B2 & "' and (Col2='Taken in' or Col2='Moved Up')";0)))

The formula above is used as outer query in report_Groups!D2 (also in D3, D4—with appropriate indeces). IFERROR is intended to display nothing if query result is #N/A.

That query displays the needed results as you can see in report_Groups tab. But as the query on step B searches the whole columns of inner_report_Groups, there's only a single given date can be analysed (or the query interim results for other given dates should be placed in different columns of inner_report_Groups or at the different tab. Is there any way to give an alias for an interim result to refer it in a single cell formula instead of keeping it on different tab?