I have a table that only contains pay dates:

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!

CodePudding user response：

This isn't necessarily going to be performant (depending on the actual table), but you could try something like this:

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

We only ever get the PayDates from the current month, and then order them by if they're greater than the current date or not, and the number of days different from the current date to figure out which is the next one to use.

CodePudding user response：

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