Finding the latest date with specific criteria

Without seeing the actual data (or a mockup of it) it is hard to give complete guidance, but here are a few things to note, followed by a cleaned up SQL statement:

1) Any field this in your select statement that is not aggregated, must be included in your group by clause. I noticed Program Title was not included in the group by clause.

2) I'm guessing you aren't returning any rows with this select statement. This is likely due to the fact that you are requiring each row to be equal to 3 different program id's. This would not be possible. You should use the IN statement instead if you are looking for rows with these 3 id's. 

Here's a suggested SQL statement:

SELECT `Agent Code`, `Program Title`, MAX(`Program Enrollment Completion Date`) as "RTS Date"
FROM `bridge_program_enrollment_status`
WHERE `Program ID` IN ('16','12','64')
AND `Program Enrollment Status 3` = 'Complete'
GROUP BY `Agent Code`, `Program Title`

Hope this helps

Yes, dropping the Program Title from your select and group by statement will definitely help.

EDIT:: video response (20 min) https://youtu.be/9uNv1_0XXao

EDIT:: oops sorry ... just saw that this was a magicETL dataflow.  read my SQL example and i'll post the Magic version below.

OOOOH a fun brain teaser!

so step 1) build a query that just extracts the last time i completed the 3 programs.  we GROUP BY userID and Program, just in case it's possible to complete a survey multiple times.

in step 2) we'll use a HAVING clause to only keep the rows where you have 3 responses, indicating that you completed 'the right number' of training.

SELECT
userID
, count(*) as NumberOfCompletedPrograms
, max(CompletionDate) as 'Date all 3 completed'

FROM (
// step 1 find the last date you completed each certification
SELECT
userID
, max(completion date) as CompletionDate
, programID
FROM 
table
WHERE
programID in (list of programs)
GROUP BY
programID, userID
) as result

// group by user b/c ... i want one row per user.
GROUP BY
UserID

// only keep the rows where you completed a total of 3 programs
HAVING
count(*) = 3

side note... a lazy developer would build a lookup table for all the trainings with a 1 or a 0 for 'isRequired.  That way you don't have to maintain the code, all you have to do is add new ProgramIDs to the list and change the isRequired flag..

then you could rewrite the query as 

SELECT
userID
, count(*) as NumberOfCompletedPrograms
, max(CompletionDate) as 'Date all 3 completed'

FROM (
// step 1 find the last date you completed each certification
SELECT
userID
, max(completion date) as CompletionDate
, programID

FROM 
training_table tt
JOIN
lu_listOfPrograms lu
ON
tt.ProgramID = lu.programID
WHERE
lu.isRequired = 1

GROUP BY
programID, userID
) as result

// group by user b/c ... i want one row per user.
GROUP BY
UserID

// only keep the rows where you completed a total of 3 programs
HAVING
count(*) = (select count(*) from lu_listOfPrograms where isRequired = 1)

Magic Version

So actually, for Magic the process is still the same.

your HAVING clause is just a FILTER after you apply a GROUP BY.

If it were me, I would avoid aggregating the data like this because you can't answer 'what am i missing from my training'?

OPTION 1)

Use the PIVOT / rows-to-columns transform to spread all the required courses as a columnName with the Date Completed as the Value.  That way you can create a visualization that shows the ones you haven't completed.

OPTION 2)

Just Output my stage 1 subquery.  That way you can create a filtered (and actionable) list of people and the courses they still need to complete.  You can do most of what you initially asked for in Cards without pre-aggregating the data.  (Pre-aggregating like your requirement is not recommended because your data won't respond to filters (which users completed training 1 but did not complete training 2.)