选择当前月和当年即将到来的支付日期

Question

我有一个只包含工资日期的表:

支付日期03/05/202203/25/202204/20/2022

Answer 1

这不一定是性能(取决于实际的表)，但您可以尝试这样做:

INSERT INTO @PayDates (PayDate) VALUES
('03/05/2022'),
('03/25/2022'),
('04/20/2022')
DECLARE @CurrentDateTime DATETIME = '2022-04-01'
SELECT TOP 1 PayDate
FROM @PayDates 
WHERE DATEPART(MONTH,PayDate) = DATEPART(MONTH,@CurrentDateTime)
AND DATEPART(YEAR,PayDate) = DATEPART(YEAR,@CurrentDateTime)
GROUP BY PayDate
ORDER BY CASE WHEN PayDate < @CurrentDateTime THEN 1 ELSE 0 END, 
CASE WHEN MAX(CASE WHEN PayDate < @CurrentDateTime THEN 0 ELSE 1 END) = 0 THEN DATEDIFF(DAY,PayDate,@CurrentDateTime) ELSE 0 END,
PayDate    

PayDate
-------
2022-04-20 00:00:00.000

我们只从当前月份获得PayDates，然后根据它们是否大于当前日期以及与当前日期不同的天数对它们进行排序，以确定下一个要使用的日期。

Answer 2

尝试下面使用聚合的示例，这对您有用吗?

declare @date date = '20220301'; /* test date */
with t as (
/* test data */
select Convert(date, pd) pd from (values ('20220305'),('20220325'),('20220420'))d(pd)
)
select 
case when max(Day(@date) - day(pd)) <= 0 then Min(pd) else Max(pd) end
from t
where Year(@date) = Year(pd)
and Month(@date) = Month(pd);