question

I have some accounts and payments based on a user. In a date range I need to sum the balance for the user. My problem is that when a user has made more than one payment in the date range, I need to get the MAX date of that. And that in SQL always returns the MAX date, not the specified date. Example: If I have two payments, one was made on the 28th of February, and the other was made on 3rd of March, when I define the query to be less or equal to the first of March, it should return 28th of February, but because of grouping it returns 3rd of March. This is my query: SELECT Z1.korisnik_id, Korisnik.imePrezime, Z1.datumRacuna, Z1.uDatum, Z1.dug, Z1.sumUplata, (Z1.dug-Z1.sumUplata) AS Balance, Z1.zgrada_id FROM (SELECT Z.korisnik_id, Z.sCena AS dug,SUM(UnosUplata.iznos) AS sumUplata,Z.datum AS datumRacuna, MAX(UnosUplata.datum) AS uDatum,Z.zgrada_id FROM ( SELECT korisnik_id, datum, SUM(cena) AS sCena,zgrada_id FROM ( SELECT korisnik_id,datum,cena, zgrada_id FROM Racun UNION SELECT korisnik_id,datum,cena, zgrada_id FROM RacunP ) AS a GROUP BY korisnik_id,datum, zgrada_id ) AS Z LEFT JOIN UnosUplata ON Z.korisnik_id = UnosUplata.korisnik_id WHERE Z.zgrada_id=1 GROUP BY Z.korisnik_id, Z.datum, Z.sCena,Z.zgrada_id ) AS Z1 INNER JOIN Korisnik ON Z1.korisnik_id = Korisnik.ID WHERE (((Z1.uDatum)<"3/01/2018")) ORDER BY Z1.korisnik_id;