Taher.se

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';