formule

select a.ncli, max(int(substr(a.douvc,1,4))-2000), substr(a.dcloc,1,4) ,
c.ntie-10000000, max(int(substr(e.douvc,1,4))-2000), int(round(mtinv/100000)), max(c.crcrm)

from mirprod.fcclie a
left join mirprod.gshsim b on
(a.ncli=b.ncli and
dtest=(select max(dtest) from mirprod.gshsim d
where d.ncli=a.ncli and mtinv > 0))
left join mirprod.fcticl k on k.ncli=a.ncli
left join mirprod.fcticr c on c.ntier=k.ntie
left join mirprod.fctier e on e.ntie =c.ntie
where a.ccacl > 200 and a.ccacl < 700
and (a.dcloc = 0 or a.dcloc >20220101)
and b.CMOIN=5
group by a.ncli, substr(a.douvc,1,4), substr(a.dcloc,1,4),
c.ntie, substr(e.douvc,1,4), mtinv
order by mtinv desc , a.ncli, c.ntie