Calculating Week Buckets in SQL
I needed to be able to create static week buckets within a single month to be able to group some fact measures by time period. The below accepts a date and then determines the buckets for that month. This will also correctly handle leap years as well. This can be used to join to fact tables for grouping into week buckets. DECLARE @date DATE = '2020-02-12' ; WITH weekcalendar AS ( SELECT * FROM ( SELECT 1 as WeekNum, DATEFROMPARTS ( YEAR ( @date ), MONTH ( @date ), 1 ) AS WeekStartDate , DATEFROMPARTS ( YEAR ( @date ), MONTH ( @date ), 7 ) AS WeekEndDate UNION SELECT 2 as WeekNum, DATEFROMPARTS ( YEAR ( @date ), MONTH ( @date ), 8 ) AS WeekStartDate , DATEFROMPARTS ( YEAR ( @date ), MONTH ( @date ), 14 ) AS WeekEndDate UNION SELECT 3 as WeekNum, DATEFROMPARTS ( YEAR ( @date ), MONTH ( @date ), 15 ) AS WeekStartDate , DATEFROMPARTS ( YEAR ( @date ), MONTH ( @date ), 21 ) AS WeekEndDate UNION SELECT 4 as WeekNum, DATE