Handle Weeks that Overlap Years in a SQL Server Date Dimension

By: Ray Barley   |   Updated: 2008-01-29   |   Comments (11)   |   Related: > Analysis Services Dimensions

Problem

Our business users want to report on some fact data aggregated by week.  They define a week as beginning on Sunday and ending on Saturday without regard to the year.  An example query would be how many employees were hired in each of the previous four weeks?   While SQL Server has the DATEPART function which has an option to give the week number in the year for any given date, the week number resets to 1 when the year changes.   Can your think of an easy way to implement this type of functionality where we can determine a week number then simply add or subtract a number of weeks to come up with our range of weeks?

Solution

There are probably a number of possible solutions but the one that comes to mind is to add a cumulative week number column to the date dimension.  This column will be assigned a sequential number representing the week; i.e. every day in a Sunday through Saturday range is assigned the same number then increment the number on each Sunday.  We will also create a function that will return the week number for any given date.  After determining the week number of a particular date, we can use a simple BETWEEN in our WHERE clause to filter the date dimension on a range of weeks and join to the relevant fact table.

Let's walk through the steps to implement the Cumulative Week column. 

CREATE TABLE dbo.dim_Date (
    wk_Date INT IDENTITY NOT NULL,
    FullDate DATETIME NOT NULL,
    WeekDayName VARCHAR(9) NOT NULL
)
GO

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @CurDate DATETIME
SET @StartDate = '2007-01-01'
SET @EndDate = '2008-12-31'
SET @CurDate = @StartDate
WHILE @CurDate <= @EndDate
BEGIN
 INSERT INTO dbo.dim_Date 
   (FullDate, WeekDayName) 
 VALUES
  (@CurDate, DATENAME(weekday, @CurDate))
 SET @CurDate = DATEADD(DAY, 1, @CurDate)
END   
GO

Now let's add the CumulativeWeek column to the date dimension and populate it.  We want to assign the value 1 to the first week in the date dimension then increment from there.  Remember the week begins on Sunday.  We get the date of the first Sunday in the date dimension, use a common table expression to calculate the number of days between each date and the first Sunday, then assign the week number by dividing the number of days by 7 and adding 1.

ALTER TABLE dbo.dim_Date
ADD CumulativeWeek INT NOT NULL
CONSTRAINT DF_dim_Date_CumulativeWeek DEFAULT 0 WITH VALUES;
GO

DECLARE @Week1Date DATETIME
SELECT @Week1Date = MIN(FullDate) 
FROM dim_Date WHERE WeekDayName = 'Sunday' ;
WITH cte_DayCounter AS
(
  SELECT wk_Date, 
         DATEDIFF(dd, @Week1Date, FullDate) Days
  FROM   dbo.dim_Date
)
UPDATE dim
SET CumulativeWeek = 
  CASE WHEN c.Days >= 0 THEN (c.Days / 7) + 1
       ELSE 0 END
FROM dbo.dim_Date dim
JOIN cte_DayCounter c ON c.wk_Date = dim.wk_Date
GO

Our next task is to create a function that will return the CumulativeWeek for any given date.  The function accepts a DATETIME parameter, truncates the time portion, joins to the date dimension to retrieve the CumulativeWeek, then returns the week.

CREATE FUNCTION dbo.udf_GetCumulativeWeekForDate(
@Date DATETIME)
RETURNS INTEGER
AS
BEGIN
    DECLARE @Week INTEGER
    SELECT  @Week = ISNULL(CumulativeWeek, 0)
    FROM    dbo.dim_Date
    WHERE   FullDate = 
            CONVERT(DATETIME,CONVERT(VARCHAR(10),@Date,120))
    RETURN  @Week   
END

6. Test the function with some sample dates:

SELECT '2007-12-29' AS Date ,DATENAME(weekday, '2007-12-29') AS WeekDay ,dbo.udf_GetCumulativeWeekForDate('2007-12-29') AS CumulativeWeek UNION ALL SELECT '2007-12-30' ,DATENAME(weekday, '2007-12-30') ,dbo.udf_GetCumulativeWeekForDate('2007-12-30') UNION ALL SELECT '2008-01-01' ,DATENAME(weekday, '2008-01-01') ,dbo.udf_GetCumulativeWeekForDate('2008-01-01') UNION ALL SELECT '2008-01-06' ,DATENAME(weekday, '2008-01-06') ,dbo.udf_GetCumulativeWeekForDate('2008-01-06')

To complete our example, assume that we want to determine how many employees have been hired in each of the previous four weeks.   We'll create a simple fact table that has the hire date which is an integer that joins to the date dimension.  We'll populate the fact table with some sample data and create our query.  We get the CumulativeWeek of the current date (assume January 22, 2008), then set our CumulativeWeek range to between CumulativeWeek - 4 and CumulativeWeek - 1.

CREATE TABLE dbo.fact_EmployeeActivity (
    wk_EmployeeActivity INT IDENTITY NOT NULL,
    fk_HireDate INT NOT NULL
)
GO

INSERT INTO dbo.fact_EmployeeActivity(fk_HireDate)
SELECT 358 UNION ALL
SELECT 359 UNION ALL
SELECT 360 UNION ALL
SELECT 361 UNION ALL
SELECT 365 UNION ALL
SELECT 366 UNION ALL
SELECT 367 UNION ALL
SELECT 372 UNION ALL
SELECT 373 UNION ALL
SELECT 383 
GO

DECLARE @BeginWeek INTEGER
DECLARE @EndWeek INTEGER
DECLARE @CurWeek INTEGER
SELECT @CurWeek = 
 dbo.udf_GetCumulativeWeekForDate('2008-01-22')
SET @BeginWeek = @CurWeek - 4
SET @EndWeek = @CurWeek - 1
SELECT 
 CumulativeWeek
,COUNT(*) AS EmployeesHired
FROM
 dbo.fact_EmployeeActivity f
JOIN
 dbo.dim_Date d ON d.wk_Date = f.fk_HireDate
WHERE
 d.CumulativeWeek BETWEEN @BeginWeek AND @EndWeek
GROUP BY CumulativeWeek
ORDER BY CumulativeWeek
GO

10. Query results:

Next Steps

• Try out the sample code and refer back to this tip if you have a date dimension requirement to handle periods that span a year.
• Remember that a successful data warehousing project has to make querying easy for ordinary business users who use reporting tools (not straight T-SQL).  So coming up with a simple way to filter queries is a big plus.
• There are many groupings based on dates; take a look at the DATEPART and DATENAME functions in  SQL Server Books Online to get some ideas for other values that would be useful in your date dimension.

About the author

Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips