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
There are several ways to do what you request.
Apply operator
By matching the outer set with the inner one, it will return a row for each of the outer ones. So, from the outside you have to return the rows by id, and the apply operator will bring the data relative to the maximum date.
I have changed the id in, because in your query the ids that you have put as an example do not exist.
Max
The process is very similar, but there is some difference, like if there are two records that match a date. You would have two rows and with Apply only one.
Otherwise it is mounted in the same way. An outer set related to an inner set.
Apply operator
Correlative subquery and other ways to achieve it
You could try to use the ORDER BY command, and if you only want one record you can use the TOP, it would be something like this:
I hope it works.