SQL logic for same day last year-CodePudding

What logic would I use in SQL to find the closest same-day in a previous year.

For example, for the date Sat 17/06/2023, in 2022 the same day is Sat 18/06/2022. This is an expected result. In 2019, the same day is Sat 22/06/2019 but Sat 15/06/2019 is in fact a closer date (to the original date of 17/06/2023), so a better comparison for us.

So not only do I want to find the same day last year, but it has to be the closest same day.

As a second take on it, how would I add a condition to consider the same day within the same month only.

CodePudding user response：

Here's a suggestion for something you can try.

It appears you want the date that has the same day based on the absolute difference in days from the current date for the same date in a different year.

The following proof-of-concept uses a simple numbers table to find the day names for the days either-side of the selected date.

I also use it just to generate the test data results for the previous 6 years from the current date.

It returns the date for each previous year and the corresponding closest date by absolute number of days.

with n as (select v from (values (-6),(-5),(-4),(-3),(-2),(-1),(0),(1),(2),(3),(4),(5),(6))x(v))

select wd.PastDate, 
  DateAdd(day, wd.v, pastdate) ClosestDate, 
  /* this is just to validate the chosen closest date is the correct day */
  DateName(weekday, DateAdd(day, wd.v, pastdate)) DayOfSelectedClosestDate
from n
cross apply (
  select nd.v, pastdate, Row_Number() over(partition by pastdate order by Abs(nd.v)) rn
  from n nd
  cross apply(values(DateAdd(year, n.v, Convert(date, GetDate()))))x(pastdate)
  where DateName(weekday, DateAdd(day, nd.v, pastdate)) = DateName(weekday, GetDate()) 
)wd
where n.v < 0 --<< Just used for selecting the previous 6 years for sample data 
  and rn = 1
order by PastDate;

Demo DBFiddle