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:
- Changed object names to English
- Added dayInYear
- Use ISO_WEEK instead of WEEK (gives Swedish/Norwegian week behavior)
- Made it into a procedure
- Set start and end date (instead of creating a certain number of days)
- 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';