I am currently trying to optimize a script, I have the following query
DECLARE @ProcessDate DATETIME = '20220930'
SELECT
Id, MAX(Fecha) Fecha , Balance
FROM Cuentas
WHERE Fecha <=@ProcessDate
AND Id IN ('60159','59973')
GROUP BY Id,Balance
which shows me the following result
Id Fecha Balance
5997321 2010-08-27 0
6015967 2012-07-12 0
6015967 2008-01-17 0.01
6015967 2007-11-09 1.8
6015967 2007-11-30 4.85
6015967 2007-12-31 4.87
5997321 2007-10-30 500
5997321 2007-10-31 500.13
5997321 2007-11-30 502
5997321 2007-12-31 503.95
5997321 2008-01-31 505.9
5997321 2008-02-29 507.73
5997321 2008-03-31 509.7
5997321 2008-04-30 511.61
5997321 2008-05-31 513.59
5997321 2008-06-30 515.52
5997321 2008-07-31 517.52
5997321 2008-08-31 519.52
5997321 2008-09-30 521.67
5997321 2008-10-31 523.91
5997321 2008-11-30 526.09
5997321 2008-12-31 528.36
5997321 2009-01-31 530.63
5997321 2009-02-28 532.7
5997321 2009-03-31 534.99
5997321 2009-04-30 537.22
5997321 2009-05-30 539.53
5997321 2009-06-30 541.78
5997321 2009-07-31 543.65
5997321 2009-08-31 545.52
5997321 2009-09-30 547.34
5997321 2009-10-31 549.22
5997321 2009-11-30 551.05
5997321 2009-12-31 552.83
5997321 2010-01-30 554.62
5997321 2010-04-29 872.73
5997321 2010-05-01 883.87
5997321 2010-06-01 886.91
5997321 2010-07-01 889.87
5997321 2010-08-01 892.93
6015967 2007-10-11 3032.55
6015967 2007-10-31 3046.8
5997321 2010-04-06 3572.73
5997321 2010-02-25 4584.62
5997321 2010-03-01 4587.58
5997321 2010-03-18 4587.91
5997321 2010-04-01 4602.73
6015967 2007-08-10 5000
6015967 2007-08-31 5013.75
6015967 2007-09-29 5032.55
It brings me all the history by Id for the selected fields even and placing the Max to the date, what I want is to bring a single row by Id always with the maximum date and that is less than or equal to the @ProcessDate variable, for example this this is the result i expect
Id Fecha Balance
6015967 2012-07-12 0.00
5997321 2010-08-27 0.00
How can I do this, since the max did not work for me, if someone helps me