Fetch week numbers with date range of each week for a whole year

Question

Using SQL Server, I need to fetch the date ranges for every week by week number for a whole year. The first day of week needs to be Monday, unless the week starts in another year, then I need it to start at the first day of the year.

For Example,

if I call the select with the year 2021 as the parameter, I would like the output to be something like that :

WeekNumbers:      DateStart:        DateEnd:
1                 2021-01-01        2021-01-03
2                 2021-01-04        2021-01-10
...
...
52                2021-12-20        2021-12-26
53                2021-12-27        2021-12-31

Please notice the first and the last week number of the year of the example.

Thanks

Answer 1

There are a lot of ways to solve this problem. A brute force approach generates all the dates using a some method -- this uses a recursive CTE -- and then assigns the weeks based on a window function counting the Mondays.

with cte as (
      select datefromparts(2021, 1, 1) as dte
      union all
      select dateadd(day, 1, dte)
      from cte
      where dte < datefromparts(2021, 12, 31)
     )
select weeknum, min(dte), max(dte)
from (select cte.*,
             sum(case when datename(weekday, dte) = 'Monday' then 1 else 0 end) over (order by dte) as weeknum
      from cte
     ) cte
group by weeknum
option (maxrecursion 0);

This is fine for a one-off query, but isn't the most efficient method to generate the weeks. It is, however, a fun illustration of recursive CTEs. That said, you should probably really have a calendar table of some sort that encapsulates this information as a table.