question

tricman10 avatar image
tricman10 asked

query with max date

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;
sql server 2012datetimeaccessdatemaxdate
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
tricman10 avatar image
tricman10 answered
I have found the solution. My mistake was that I was putting the max date in the range. This is the solution for the 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.datum AS datumRacuna, Z.sCena AS dug, SUM(UnosUplata.iznos) as sumUplata,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 AND UnosUplata.datum <= '2/25/2018' 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.datumRacuna >= '2/2/2018' ORDER BY z1.korisnik_id
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This is exactly what I was about to post :) But glad to see you solved it yourself.
0 Likes 0 ·
But one thing I must ask, why LEFT JOIN with the table "UnosUplata" when you have the filter **UnosUplata.datum
0 Likes 0 ·
I need the left join because there are cases when I have some accounts(Racun and RacunP), but there are no payments(UnosUplata) yet. And I need to see that accounts also. If there is a inner join,I wouldn't get the accounts without payments.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.