Есть запрос (ниже)
Задача состоит в том, что бы выбрать все записи до 4021-01-04, а потом отобрать максимальное значение Периода в группировке ГодУрожая и Поле
Но запрос работает так, что сначала выбираются максимальные значения, а потом берется фильтр по дате
В таком случае , если есть такие записи , к примеру ,

Поле _Period ГодУрожая
ДПт-Агб-206.0.0 26.06.2019 2020
ДПт-Агб-206.0.0 29.07.2019 2020
ДПт-Агб-206.0.0 10.12.2019 2020
ДПт-Агб-206.0.0 21.09.2020 2021
ДПт-Агб-206.0.0 13.04.2021 2021

Запрос сработает, так что выведет вот это
_Description _Period ГодУрожая
ДПт-Агб-206.0.0 10.12.2019 2020

Он взял максимум по 2021 году за 13.04.2021, а потом сработал фильтр все записи период до 4021-01-04, соответственно
я не получил запись за 21.09.2020, а нужно))
Как можно переписать запрос или как-то изменить порядок действий в логике? Спасибо за помощь



SELECT DISTINCT

CAST(DATEADD(YEAR, - 2000, СостояниеПолей._Period) AS date) AS Дата,
Поля._Description AS Поле,
СостояниеПолей._Fld27936 AS Площадь,
LEFT(CAST((CASE WHEN СостояниеПолей._Fld27941 <= '4001-01-01 00:00:00.000' THEN СостояниеПолей._Fld27941 ELSE Dateadd(YEAR, - 2000, СостояниеПолей._Fld27941) END) AS date), 4) AS ГодУрожая
FROM
[_InfoRg27934] СостояниеПолей LEFT OUTER JOIN
[_Reference343] ПодразделенияОрг RIGHT OUTER JOIN
[_Reference189] Поля ON ПодразделенияОрг._IDRRef = Поля._OwnerIDRRef ON СостояниеПолей._Fld27935RRef = Поля._IDRRef LEFT OUTER JOIN


WHERE
(СостояниеПолей._Period <= DATEADD(month, DATEDIFF(month, 0, CONVERT(DATETIME,'4021-01-04 00:00:00.000')), 0) )
AND Поля._Description='ДПт-Агб-206.0.0'
AND EXISTS
(SELECT 2 AS Expr1
FROM [_InfoRg27934] AS t2
WHERE (_Fld27941 = СостояниеПолей._Fld27941) AND (_Fld27935RRef = СостояниеПолей._Fld27935RRef)
GROUP BY _Fld27941, _Fld27935RRef
HAVING (СостояниеПолей._Period = MAX(_Period)))

Владислав Колосов,

так тут же МАХ и используется, но порядок действий не подходит
Не совсем понимаю, как сюда можно OVER применить