SugarQuery Having Max(datefield) < xxxx-xx-xx


I need to create a query which selects contacts which only having cases older then one year.

I managed to create a working MySQL query, but how to convert it to a SugarQuery? Anyone any experience with this type of query?


SELECT, MAX(jt0_cases_cstm.dat_start_c)
FROM contacts
INNER JOIN contacts_cases contacts_cases ON ( = contacts_cases.contact_id) AND (contacts_cases.deleted = 0)
INNER JOIN cases jt0_cases ON ( = contacts_cases.case_id) AND (jt0_cases.deleted = 0)
LEFT JOIN cases_cstm jt0_cases_cstm ON jt0_cases_cstm.id_c =
LEFT JOIN contacts_cstm contacts_cstm ON contacts_cstm.id_c =
WHERE (contacts.deleted = 0)
HAVING (MAX(jt0_cases_cstm.dat_start_c) <= '2022-11-01')


$SQ = new SugarQuery();
$cases = $SQ->join('cases')->joinName();
$havingCondition = new SugarQuery_Builder_Condition($SQ);
// conceptual code, not working...
// $havingCondition->max("$cases.dat_start_c")->setOperator('<')->setValue('2022-11-01');