CODEWITHSUNDEEP
sql-serverdatabasedimensional-modeling
SMC
SMC

Reputation: 3

Creating a time dimension with specific dates on SQL

I'm making an analysis project on football transfers. I have a model with one Fact Table called FactTransfers and I need to link it to a time dimension but i need a specific range of dates, namely the dates where it's possible to transfer players (from June 1st to September 1st and from January 1st to January 31st). I have seen some posts related to the matter but they all have code and attributes that I don't need.

Basically what i want is: Date as primary key, Day of the month, Name of the month, Transfer window (summer or winter), Year.

I'm not too familiarized with sql code and I have spent hours trying to figure it out without the results I need. Thank you in advance for all your help!

Upvotes: 0

Views: 2103

Answers (1)

Sandesh
Sandesh

Reputation: 1044

Here is the code to create and populate your Dim table for Dates. Hope this helps.

CREATE TABLE [dbo].[DimDate]
(
    [DateKey] INT primary key, 
    [Date] DATETIME,
    [DayofMonth] TINYINT, -- Field will hold day number of Month
    [NameofMonth] VARCHAR(9),--January, February etc
    [TransferWindow] VARCHAR(20), -- Summer & Winter
)

--Specify Start Date and End date here
--Value of Start Date Must be Less than Your End Date 

DECLARE @StartDate DATETIME = '01/01/2015' --Starting value of Date Range
DECLARE @EndDate DATETIME = '12/31/2025' --End Value of Date Range
DECLARE @CurrentDate AS DATETIME = @StartDate

WHILE @CurrentDate < @EndDate
BEGIN

    INSERT INTO [dbo].[DimDate]
    SELECT
        CONVERT (char(8),@CurrentDate,112) as DateKey,
        @CurrentDate AS Date,
        DATEPART(DD, @CurrentDate) AS [DayOfMonth],
        DATENAME(MM, @CurrentDate) AS [MonthName],
        CASE WHEN (MONTH(@CurrentDate) BETWEEN 6 AND 8) OR ( MONTH(@CurrentDate) =9 AND DATEPART(DD, @CurrentDate)=1) THEN 'Summer'
             WHEN MONTH(@CurrentDate) =1 THEN 'Winter'
             ELSE ''
        END AS [TransferWindow]

    SET @CurrentDate = DATEADD(DD, 1, @CurrentDate)
END

SELECT * FROM [DimDate]

--DROP TABLE [DimDate]

Upvotes: 1

Related Questions