Select the Upcoming Pay Date of the Current Month AND Year

Question

I have a table that only contains pay dates:

Pay Date
03/05/2022
03/25/2022
04/20/2022

Sometimes there are two pay dates in the same month. I’d like for my query to return the upcoming pay date for the current month and year, but after the last pay date of the month I would like for the query to continue to return the last pay date of the month until we enter the new month.

So, from March 1st to the 5th it should return: 03/05/2022

From the 6th UNTIL the end of March, it should return: 03/25/2022

Then in April, it will return the April date throughout the entire month of April.

I’ve tried the following, however it will not return the last pay date in March if it is run after the 25th:

SELECT TOP (1)
    [Pay Date]
FROM PayTable
WHERE
    MONTH([Pay Date]) = MONTH(‘3/26/2022’/*GETDATE()*/)
    AND YEAR([Pay Date]) = YEAR(‘3/26/2022’/*GETDATE()*/)
    AND DAY([Pay Date]) >= DAY(‘3/26/2022’/*GETDATE()*/)
ORDER BY [Pay Date] ASC

I’m a bit stuck on how to get the desired result and appreciate any help I can get. Thanks!

Answer 1

Try the following example using aggregation, does this work for you?

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);