SQL: Can we make query to compare data for 2 different years in same month, but looking at the same day as priority

FYI: I'm using SQL Server 2012.

Because scope of the function is a selling date which is different by the days of week (like comparison for customer visiting percentage).

So the main key to compare between last-this year, is to check the same week or same day that most related to the month and are same day (Monday, Tuesday, ...)



For example:

2018/11/01 should compare to 2017/11/02 (Thu)
2018/11/02 should compare to 2017/11/03 (Fri)
...
2018/11/30 should compare to 2017/12/01 (Fri)

I can't imagine the syntax out of my head.
What is syntax should I write?
What are the keywords, functions should I call to achieve this scope?

Anyone who has experience about some kind of function like this, please help.
All comments would be appreciate.

If I correctly understand your question, you can use two (ore more) ways but both are related with DATEPART function.

DECLARE @Date1 datetime = '20181101';
DECLARE @Date2 datetime = '20170101';

SELECT DATEPART(DAYOFYEAR, @Date1) DayOfYear,
       DATEPART(WEEK, @Date1) WeekNumber,
       DATEPART(WEEKDAY, @Date1) WeekDay,
       @@DATEFIRST FirstDayOfWeek;

DayOfYear | WeekNumber | WeekDay | FirstDayOfWeek
--------: | ---------: | ------: | :-------------
      305 |         44 |       5 | 7

Using DayOfYear

SELECT DATEADD(DAY, DATEPART(DAYOFYEAR, @Date1), @Date2) AS UsingDayOfYear;

| UsingDayOfYear      |
| :------------------ |
| 02/11/2017 00:00:00 |

Using same week & same day of week

This depends on first day of week:

SET DATEFIRST 1;

SELECT DATEADD(DAY, ((DATEPART(WEEK, @Date1) - 1) * 7) + DATEPART(WEEKDAY, @Date1), @Date2) UsingSameMonthSameDayOfWeek;

UsingSameMonthSameDayOfWeek |
| :-------------------------- |
| 02/11/2017 00:00:00         |

You can use that function to add another combination, for example: First Thursday of November.

db<>fiddle here

StackExchange Database Administrators Q#222933, answer score: 4