'How can I create a week group column in my Date Dimension Table based on this specific logic?

I am using SQL Server 2014 and I have a table in my Database called Date Dimension.

It has a column called "Date" which contains daily dates from 01 January 2013 to 31 December 2025 (extract of the column given below):

Date
2022-01-01
2022-01-02
2022-01-03
2022-01-04
2022-01-05
2022-01-06
2022-01-07
2022-01-09
...
2022-01-30
2022-01-31
...

I need to create a new column in the Date Dimension Table called "HR_WeekGroup" based on the following logic:

A WeekGroup will start on a Monday and end on a Sunday. WeekGroups will be calculated for EACH Month separately. That is, if the 1st Day of a Month is a Saturday, the WeekGroup for that week will consist of only 2 days (Saturday and Sunday) and if the last Day of that Month is a Monday, the weekGroup will consist of only 1 Day (Monday).

Here is what I'm after (based on the Month of January 2022):

   Date          HR_WeekGroup
2022-01-01       Wk 01Jan2022-02Jan2022
2022-01-02       Wk 01Jan2022-02Jan2022
2022-01-03       Wk 03Jan2022-09Jan2022
2022-01-04       Wk 03Jan2022-09Jan2022
2022-01-05       Wk 03Jan2022-09Jan2022
2022-01-06       Wk 03Jan2022-09Jan2022
2022-01-07       Wk 03Jan2022-09Jan2022
2022-01-09       Wk 03Jan2022-09Jan2022
...              ...
2022-01-30       Wk 24Jan2022-30Jan2022
2022-01-31       Wk 31Jan2022
...

What would be the T-SQL Code that would allow me to create this column?

I had a look at the following pages but I can't figure out how to apply the examples to my specific problem.

What is the SQL syntax to create a column in my Date Dimension Table that will group the dates into this specific week grouping?

How to Group Data by Week in SQL Server

sqlsql-serverdatetsql


Solution 1:[1]

Set DateFirst 1;
-- The above changes the start of the week to Monday.
-- This makes the week as starting on Monday and ending on Sunday
-- DayOfWeek 1 means Monday
-- DayOfWeek 7 means Sunday
with cte as (
    select 
        Date_Column, 
        MONTH(Date_Column) as [MONTH],  -- Month in the year
        DAY(Date_Column) as [DAY], -- Day in the month
        DATEPART(week,Date_Column) as [WeekOfTheYear], --Week number of the year
        DATEPART(dw,Date_Column) as [DayOfWeek], -- The week day of the week
        Min(Date_Column) OVER (Partition by DATEPART(week,Date_Column), MONTH(Date_Column), YEAR(Date_Column) ) as [MinDateWeekGroup],
        Max(Date_Column) OVER (Partition by DATEPART(week,Date_Column), MONTH(Date_Column), YEAR(Date_Column) ) as [MaxDateWeekGroup]
    from date_dimension
    --order by YEAR(Date_Column), MONTH(Date_Column), DAY(Date_Column)  
)
select 
    Date_Column,
    case when [MinDateWeekGroup] = [MaxDateWeekGroup] 
        THEN CONCAT('Wk ',
    Right('0'+Convert(varchar(10), Day([MinDateWeekGroup])),2),
    Convert(char(3), [MinDateWeekGroup], 0),Convert(char(4),YEAR([MinDateWeekGroup])) )
        ELSE 
    CONCAT('Wk ',
    Right('0'+Convert(varchar(10), Day([MinDateWeekGroup])),2),
    Convert(char(3), [MinDateWeekGroup], 0),Convert(char(4),YEAR([MinDateWeekGroup])),
    '-',
    Right('0'+Convert(varchar(10), Day([MaxDateWeekGroup])),2),
    Convert(char(3), [MaxDateWeekGroup], 0),Convert(char(4),YEAR([MaxDateWeekGroup]))
    ) 
    END as [HR_WeekGroup]
from cte
order by YEAR(Date_Column), MONTH(Date_Column), DAY(Date_Column)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions