sql server - SQL query to find dates of last year equivalent to today's date

Question

I am writing a SQL query to find business working dates of last year equivalent to today's date.

In this query it should fetch :-

For e.g. if today is 5th January, 2021 and it is the second day of second week of the year. So I need to find the exact equivalent date of the second day of second week of the previous year. So it would be 7th January, 2020.

And with this, I need the business working dates of that week of 7th January 2020 (i.e. excluding Saturday & Sunday) Which will come up as 2020-Jan-06 to 2020-Jan-10 according to the example.

So I will need the report between 6th Jan - 10th Jan, 2020.

I am trying to use this code to find date of last year equivalent to today's date (5th Jan, 2021 viz. second day of second week)

select Convert(date, (DATEADD(year, -1, getdate()+2))) ;

Answer 1

I have use multiple CTE to show you the step by step calculation. It should be pretty easy to follow.

Basically it find the week_no and day_no_of_week for 2021-01-05 and then use that to find the same date for 2020

declare @input_date     date = '2021-01-05',
        @year_offset    int  = -1;  -- previous year

with 
cte1 as
(
    select  input_date          = @input_date,
            week_no             = DATEPART(WEEK, @input_date),
            first_day_of_week   = DATEADD(WEEK, DATEDIFF(WEEK, 0, @input_date), 0)
),
cte2 as
(
    select  *,
            day_no_of_week = DATEDIFF(DAY, first_day_of_week, @input_date) + 1
    from    cte1 
),
cte3 as
(
    select  *,
            first_day_of_the_prev_year = DATEADD(YEAR, DATEDIFF(YEAR, 0, @input_date) + @year_offset, 0)
    from    cte2
),
cte4 as
(
    select  *,
            first_day_of_week_prev_year = DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(WEEK, week_no - 1, first_day_of_the_prev_year)), 0)
    from    cte3
)
select  *,
        DATEADD(DAY, day_no_of_week - 1, first_day_of_week_prev_year) as the_required_date
from    cte4