Creating and populating a date dimension with Swedish or Norwegian weeks

Quite often when you work with analyzing data you need to be able to see your KPI’s per day, week or some other time-frame. Both Sweden and Norway has a fairly unusual way to handle week numbers which might make it a bit difficult to find scripts that are ready to use. Here is my DimDate table and the procedure I have to populate the table.

This script is based on a post by Robin Langell.

The changes that I have made are:

  1. Changed object names to English
  2. Added dayInYear
  3. Use ISO_WEEK instead of WEEK (gives Swedish/Norwegian week behavior)
  4. Made it into a procedure
  5. Set start and end date (instead of creating a certain number of days)
  6. Made Sunday to be day number 7 (instead of day 0)

Ofcourse feel free to let me know if there are any errors in my code!

Creating the table:

use [YourDB]
go

declare @table as varchar(50)
SET @table = 'DimDate'

IF NOT EXISTS (
	select 'Y'
	from sys.tables
	where name = @table
	)

	BEGIN
		CREATE TABLE dbo.dimDate (
			ID	INT NOT NULL,
			[Day]	DATETIME NOT NULL,
			[Week]	INT NOT NULL,
			[Month]	INT NOT NULL,
			[Quarter]	INT NOT NULL,
			[Year]	INT NOT NULL,
			DayInYear INT NOT NULL,
			DayInMonth	INT NOT NULL,
			dayInWeek	INT NOT NULL,
			YearWeek	INT NOT NULL,
			MonthWithZero NVARCHAR(2) NOT NULL,
			[DayName]	NVARCHAR(10) NOT NULL,
			[MonthName]	NVARCHAR(20) NOT NULL
		CONSTRAINT PK_DimDatum_datumNyckel PRIMARY KEY (ID)
		);
		SELECT 'Table did not exist, table '+ @table +' created';
	END ELSE
		IF NOT (
			SELECT COUNT(*)
			FROM DimDate
			) > 0
		BEGIN
			Drop Table DimDate;

			CREATE TABLE dbo.dimDate (
				ID	INT NOT NULL,
				[Day]	DATETIME NOT NULL,
				[Week]	INT NOT NULL,
				[Month]	INT NOT NULL,
				[Quarter]	INT NOT NULL,
				[Year]	INT NOT NULL,
				DayInYear INT NOT NULL,
				DayInMonth	INT NOT NULL,
				dayInWeek	INT NOT NULL,
				YearWeek	INT NOT NULL,
				MonthWithZero NVARCHAR(2) NOT NULL,
				[DayName]	NVARCHAR(10) NOT NULL,
				[MonthName]	NVARCHAR(20) NOT NULL
			CONSTRAINT PK_DimDatum_datumNyckel PRIMARY KEY (ID)
			);
			SELECT 'Table '+ @table +' existed without data, table dropped and recreated';
		END ELSE
		SELECT 'Table '+ @table +' contains data, no action taken';

And then creating the procedure:

Use [YourDB]
go

IF EXISTS (
	SELECT 'Y'
	FROM sys.objects
	WHERE name = 'AddDates'
	)
	Drop proc AddDates
go

CREATE PROC AddDates
	(
	@StartDate as DateTime,
	@EndDate as DateTime
	)
AS 

  DECLARE @CurrentDate AS DATETIME
  SET @CurrentDate = @StartDate
  WHILE @CurrentDate <= @EndDate
	BEGIN
		INSERT INTO dbo.DimDate
		SELECT
		CAST(CONVERT(nvarchar(10), @CurrentDate, 112) as int) as ID,
		CAST(CONVERT(nvarchar(10), @CurrentDate, 112) as DATETIME) as [Day],
		DATEPART(ISO_WEEK,@CurrentDate) as [Week],
		MONTH(@CurrentDate) as [Month],
		DATEPART(Q,@CurrentDate) as [Quarter],
		YEAR(@CurrentDate) as [Year],
		DatePart(DayOfYear, @CurrentDate) AS DayInYear,
		DATEPART(DD,@CurrentDate) as DayInMonth,
		CASE WHEN DATEPART(WEEKDAY,@CurrentDate) -1 = 0 THEN 7
			ELSE DATEPART(WEEKDAY,@CurrentDate) -1
		END as dayInWeek,
		CASE WHEN DATEPART(WEEK,@CurrentDate) < 10 THEN CAST(YEAR(@CurrentDate) AS NVARCHAR(4)) + '0' +  CAST(DATEPART(WEEK,@CurrentDate) AS NVARCHAR(2))
			ELSE  CAST(YEAR(@CurrentDate) AS NVARCHAR(4)) + CAST(DATEPART(WEEK,@CurrentDate) AS NVARCHAR(2))
		END as YearWeek,
		CASE WHEN MONTH(@CurrentDate) < 10 THEN '0' + CAST(MONTH(@CurrentDate) AS NVARCHAR(2))
			ELSE CAST(MONTH(@CurrentDate) AS NVARCHAR(2))
		END as MonthWithZero,
		CASE
			WHEN DATEPART(WEEKDAY,@CurrentDate) -1 = 1 THEN 'Måndag'
			WHEN DATEPART(WEEKDAY,@CurrentDate) -1 = 2 THEN 'Tisdag'
			WHEN DATEPART(WEEKDAY,@CurrentDate) -1 = 3 THEN 'Onsdag'
			WHEN DATEPART(WEEKDAY,@CurrentDate) -1 = 4 THEN 'Torsdag'
			WHEN DATEPART(WEEKDAY,@CurrentDate) -1 = 5 THEN 'Fredag'
			WHEN DATEPART(WEEKDAY,@CurrentDate) -1 = 6 THEN 'Lördag'
			WHEN DATEPART(WEEKDAY,@CurrentDate) -1 = 0 THEN 'Söndag'
		END	as [DayName],
		CASE
			WHEN MONTH(@CurrentDate) = 1 THEN 'Januari'
			WHEN MONTH(@CurrentDate) = 2 THEN 'Februari'
			WHEN MONTH(@CurrentDate) = 3 THEN 'Mars'
			WHEN MONTH(@CurrentDate) = 4 THEN 'April'
			WHEN MONTH(@CurrentDate)= 5 THEN 'Maj'
			WHEN MONTH(@CurrentDate)= 6 THEN 'Juni'
			WHEN MONTH(@CurrentDate)= 7 THEN 'Juli'
			WHEN MONTH(@CurrentDate)= 8 THEN 'Augusti'
			WHEN MONTH(@CurrentDate)= 9 THEN 'September'
			WHEN MONTH(@CurrentDate)= 10 THEN 'Oktober'
			WHEN MONTH(@CurrentDate)= 11 THEN 'November'
			WHEN MONTH(@CurrentDate)= 12 THEN 'December'
		END	as [MonthName]
		SET @CurrentDate += 1
    END
go

And finally populating the table

exec AddDates '19700101','20201231';
This entry was posted in BI, IT and tagged , , , , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>