Handle Weeks that Overlap Years in a SQL Server Date Dimension

By:   |   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. 

1. Create a simple date dimension:
CREATE TABLE dbo.dim_Date (
    wk_Date INT IDENTITY NOT NULL,
    FullDate DATETIME NOT NULL,
    WeekDayName VARCHAR(9) NOT NULL
)
GO
2. Populate the date dimension with a range of dates:
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.

3. Add CumulativeWeek column to the date dimension:
ALTER TABLE dbo.dim_Date
ADD CumulativeWeek INT NOT NULL
CONSTRAINT DF_dim_Date_CumulativeWeek DEFAULT 0 WITH VALUES;
GO
4. Populate the CumulativeWeek column in the date dimension:
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.

5. Create function to return the CumulativeWeek for a date:
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')
testresults

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.

7. Create employee activity fact table:
CREATE TABLE dbo.fact_EmployeeActivity (
    wk_EmployeeActivity INT IDENTITY NOT NULL,
    fk_HireDate INT NOT NULL
)
GO
8. Populate the employee activity fact table:
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
9. Query employees hired in the last 4 weeks:
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:
queryresults
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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ray Barley 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


Comments For This Article




Thursday, May 21, 2015 - 3:12:21 AM - Ilmam Back To Top (37241)

I guess it's a bit late to comment here, but I have another approach. I think having week number in year is good to keep. I saved the week number as follows, assuming I have [YEAR] and [DATE] values:

SET [WEEK] = ([YEAR]*100) + DATEPART(WK, [DATE])

 

Now this solution has the problem of weeks spanning over two years. I need to have a uinque week number in system, so I updated the table as follows:

 

UPDATE CALENDAR SET [WEEK] = (([YEAR]-1)*100)+53
	FROM CALENDAR
	LEFT JOIN
	(
		SELECT [WEEK]
		FROM [dbo].[CALENDAR]
		WHERE   [WEEK]-[YEAR]*100=1
		GROUP BY [WEEK]    
		HAVING COUNT(*)

To explain this solution: I intended to change first week of year if it didn't have 7 days, I changed it to be the same as last week of previous year.

For example my weeks number would be as follows 201501, and since week 201501 wasn't full seven days, then I change it to 201453.

This way I don't have 201501 week in my system, and weeks order would be 201453, 201502. Which is quite fine and acceptable way to number weeks to me.

 

Monday, February 3, 2014 - 11:28:06 AM - Raymond Barley Back To Top (29318)

The logic used to assign the ***ulative week number is to get the minimum date for the first day of the week; e.g.

here is the first part of the script at 4. Populate the ***ulativeWeek column in the date dimension:

DECLARE @Week1Date DATETIME SELECT @Week1Date = MIN(FullDate) FROM dim_Date WHERE WeekDayName = 'Sunday' ;

Change this to:

DECLARE @Week1Date DATETIME SELECT @Week1Date = MIN(FullDate) FROM dim_Date WHERE WeekDayName = 'Saturday' ;

Sunday, February 2, 2014 - 9:23:53 PM - Kyle Back To Top (29313)

What if you need the Week to run from Sat to Fri?  What would need to be altered to accommodate for that?

Thursday, December 9, 2010 - 3:11:42 PM - Ray Barley Back To Top (10437)

I don't remember the specifics of the project where I did this but it was something like the week always begins on Saturday and all days in the week belong to that week even if they cross into the next month or even the next year.  As a result you occasionally wind up with a week that includes days that are in the next month (and possibly the next year).

 

 

Thursday, December 9, 2010 - 1:28:01 PM - Chuck Hottle Back To Top (10435)

I just had to implement this functionality in our DW/BI environment and also added a WeekOfYear column to the date dimension, though my code to po***te it was a little different.  In our implementation, 1/6/2008 starts week 1 of 2008.  Your example shows it as week 53.  Why is that?  Our implementation only has 52 weeks per year and, because part of the first week of the year could be in week 52 of the previous year and part of the last week of the year could be in the last week of the current year, I also added a SalesYear column to specify which year the day was actually in for sales reporting on a Sunday-Saturday week. The first 5 days of 2008 are in the SalesYear 2007, WeekOfYear 52, along with 12/30/07 and 12/31/07.  If I didn;t do that and a user selected week 52 of 2008, they would get data for some days in January and some in December.

Monday, December 7, 2009 - 5:28:30 AM - raybarley Back To Top (4523)

You are correct.  However my idea was that in a data warehousing application it makes sense to put the week number in the date dimension.  This is a table that gets built one time.  My requirement was to run a report for a range of weeks based on a singlwe week ending date;  The range of weeks would be things like current week, last 4 weeks, current week this year versus current week last year, etc.  By having the week in the dimension, you can do a simple where clause that brings back all rows with the week numbers you need.

Wednesday, December 2, 2009 - 8:04:23 AM - WiseOldMan Back To Top (4509)

I'm a little confused by looping here by adding a day:

 set @week = datediff(week,'19950101',@sdate)
    while @sdate <=@edate
    begin
        if datepart(weekday,@sdate)=1
        set @week = @week+1
        insert @weektable
        values(@sdate,@week)
        set @sdate = dateadd(dd,1,@sdate)
        end   
    return
    end


It looks like you found Sundays at each end of the range for @sdate and @edate, so why loop day by day looking for a Sunday.  As far as I know, every week since the beginning of time (as far as SQL Server is concerned) had a Sunday.  So why not just loop adding a week instead of a day. It should make your loop 7 times as efficient plus you don't have to check the DatePart.

while @sdate <=@edate
    begin
        if datepart(weekday,@sdate)=1 
        set @week = @week+1
        insert @weektable
        values(@sdate,@week)
        set @sdate = dateadd(dd,1,@sdate)
        set @sdate = dateadd(ww,1,@sdate)
        end   
    return

Then this could be made even more efficient by simply using a numbers table and avoiding the loop altogether:

insert @weektable
select dateadd(ww,TheNumber,@datein), TheNumber
from NumbersTable
where TheNumber between @weeksPre and @weeksPost

 I know, that's a bit of overkill for a small function that is typically just looking for a few weeks of Sundays, but this would scale much better to a bigger solution.

Friday, February 1, 2008 - 6:11:17 AM - raybarley Back To Top (262)

Thanks for the reply.  The function that returns a table is very elegant.  Every data warehouse has a date dimension and there are some benefits to having the CumulativeWeek column in the date dimension.  I would incorporate your function that returns the table in my solution but still keep the CumulativeWeek column in the date dimension.  I agree that based on your solution it's not necessary but there are alot of things done in data warehouses to make things easier for non-technical users and my solution would be an example of that.

 

Thanks again.

Friday, February 1, 2008 - 5:38:18 AM - ptrongo Back To Top (261)

 OK... I agree... Here is the function only version of what I submitted before.

You can use the function to group any table that has date data in it, by week.

 no need to add columns or tables.

 

Patrick 

------------------------------------------------------------------------------------------------------
--      Example Run for the previous four weeks summary
------------------------------------------------------------------------------------------------------

DECLARE @BeginWeek INTEGER
DECLARE @EndWeek INTEGER
SET @BeginWeek = -4
SET @EndWeek =  -1

DECLARE @today datetime
SET @today = getdate()

SELECT
 WR.GroupWeek
,COUNT(*) AS EmployeesHired
FROM  dbo.fact_EmployeeActivityX f
INNER JOIN udf_WeekRangesTbl(@today,@BeginWeek,@EndWeek) wr
     ON wr.CompDate = convert(char(8),f.fk_HireDate,112)
GROUP BY GroupWeek
ORDER BY GroupWeek


------------------------------------------------------------------------------------------------------
--      This function is all that you need... No need to maintain a table.
------------------------------------------------------------------------------------------------------


Create function udf_WeekRangesTbl(
@Datein datetime, -- date to base week range on
@weeksPre int = null, -- how many weeks before the current week do you want
@weeksPost int = null-- how many weeks after the current week do you want
)

returns @weekTable Table (CompDate datetime, GroupWeek int)

as
begin
   
    declare @week int
    declare @sdate datetime
    declare @edate datetime
   
    set @weeksPre = coalesce(@weeksPre, 0)
    set @weeksPost = coalesce(@weeksPost, 0)

--trim times from date passed
    select @Datein = convert(char(8),@Datein,112)

--find closest Sunday prior to or including @datein
    select @Datein =dateadd(dd,-1*(datepart(weekday,@Datein)-1), @Datein)

--calculate date range based on week parameters
    set @sdate = dateadd(dd,(@weekspre)*7,@Datein)
    set @edate= dateadd(dd,6+7*@weekspost,@Datein)

--fill @weektable
    set @week = datediff(week,'19950101',@sdate)
    while @sdate <=@edate
    begin
        if datepart(weekday,@sdate)=1
        set @week = @week+1
        insert @weektable
        values(@sdate,@week)
        set @sdate = dateadd(dd,1,@sdate)
        end   
    return
    end




------------------------------------------------------------------------------------------------------
--      Sample Data
------------------------------------------------------------------------------------------------------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fact_EmployeeActivityX]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[fact_EmployeeActivityX]
GO

CREATE TABLE [dbo].[fact_EmployeeActivityX] (
    [wk_EmployeeActivity] [int] IDENTITY (1, 1) NOT NULL ,
    [fk_HireDate] [datetime] NOT NULL
) ON [PRIMARY]
GO


declare @counter int
set @counter = 2000
while @counter > 0
begin
insert into dbo.fact_EmployeeActivityX(fk_HireDate)
select dateadd(dd,rand()*-770,getdate())
set @counter = @counter-1
end
 

Wednesday, January 30, 2008 - 7:54:40 PM - raybarley Back To Top (257)

You are correct that the week numbers are arbitrary.  However, the requirement is to come up with the easiest way to aggregate on the week number for reporting.  I think having the week number in the date dimension satisfies that requirement but certainly that is just one way of doing it.  Another part of the requirement is to support business users who are creating queries with a reporting tool; they can't use a stored procedure.  Their tool constructs a SELECT statement for them which can use tables, views or functions.

Wednesday, January 30, 2008 - 5:00:12 AM - ptrongo Back To Top (255)
Hi... my approach was that the actual week numbers were arbitrary... so why keep track of them..

My solution takes the date as an argument , you then tell the function how many weeks prior and how many weeks after  relative to the week containing the date you entered.

 
All of my weeks are relative to 19950101.
I arbitrarily chose 1/1/1995 as my initial week because that year started on a Sunday. Making it clear that week one of that year started cleanly on the first day of the year.

 

Let me know what you think....

Patrick

 

 

i.e. if you pass in '1/29/2008',0,0 your date range would be 1/27/2008 - 2/2/2008  or 1 week.

 

 

   

 


/* Example Run

declare @sdate datetime
declare @edate datetime

execute usp_getWeekRange_onDate '12/29/2006',51,0,@sdate output, @edate output

select * from udf_WeekRanges(@sdate, @edate) -- this function can be used to aggregate your data


*/






Create procedure usp_getWeekRange_onDate(
@Datein datetime = null, -- date to base week range on
@weeksPre int = null, -- how many weeks prior to the current week do you want
@weeksPost int = null, -- how many weeks after current week
@DateStart datetime output, --output param for daterange start - inclusive
@DateEnd datetime output -- output param for date range end - inclusive
)
as
begin

--defaults
set @datein = coalesce(@datein, getdate())
set @weeksPre = coalesce(@weeksPre, 0)
set @weeksPost = coalesce(@weeksPost, 0)

--trim times from date passed
select @Datein = convert(char(8),@Datein,112)

--find closest Sunday prior or including @datein
select @Datein =dateadd(dd,-1*(datepart(weekday,@Datein)-1), @Datein)

--calculate date range based on week parameters
set @DateStart = dateadd(dd,(@weekspre)*-7,@Datein)
set @DateEnd = dateadd(dd,6+7*@weekspost,@Datein)


end

 

 

create function udf_WeekRanges (@sdate datetime, @edate datetime)
returns @weekTable Table (caldate datetime, rltvWeek int)
as
begin
   
    declare @week int
    set @week = datediff(week,'19950101',@sdate)-1
    while @sdate <=@edate
    begin
        if datepart(weekday,@sdate)=1
        set @week = @week+1
        insert @weektable
        values(@sdate,@week)
        set @sdate = dateadd(dd,1,@sdate)
        end   
    return
    end














get free sql tips
agree to terms