Sql-server – Monthly Date Dimension

sql server

I am doing my first ssis and sql project for uni being new to both. I am creating a date dimension but only month and year no days. I have seen loads of examples online for date dim but including days. Is there a way to create monthly date dim as deadline is approaching and still a lot to do. All help much appreciated.

enter image description here

Cheers

Aidan

Best Answer

Without more specific requirements, this is probably where I'd start:

CREATE TABLE dbo.MonthDimension
(
  StartDate date NOT NULL,
  EndDate   AS (CONVERT(date,     DATEADD(DAY,-1,DATEADD(MONTH,1,StartDate))))
               PERSISTED,
  NumDays   AS (CONVERT(tinyint,  DATEDIFF(DAY,StartDate,DATEADD(MONTH,1,StartDate)))),
  Y         AS (CONVERT(smallint, DATEPART(YEAR,StartDate))),
  M         AS (CONVERT(tinyint,  DATEPART(MONTH,StartDate))),
  Q         AS (CONVERT(tinyint,  DATEPART(QUARTER,StartDate))),
  MonthName AS (CONVERT(varchar(20), DATENAME(MONTH,StartDate))),
  CONSTRAINT PK_MonthDimension PRIMARY KEY (StartDate, EndDate)
);

Then to populate it, just substitute your desired start and end dates:

DECLARE @start date = '20000101', 
        @end   date = '20991231';

;WITH x(n) AS 
(
  SELECT 0 UNION ALL 
  SELECT n+1 FROM x 
  WHERE n < DATEDIFF(MONTH, @start, @end)
)
INSERT dbo.MonthDimension(StartDate) 
  SELECT DATEADD(MONTH,n,@start) FROM x
  OPTION (MAXRECURSION 32767);

Partial table data:

StartDate   EndDate     NumDays  Y      M   Q   MonthName
----------  ----------  -------  ----   --  --  ---------
2000-01-01  2000-01-31  31       2000   1   1   January
2000-02-01  2000-02-29  29       2000   2   1   February
2000-03-01  2000-03-31  31       2000   3   1   March
2000-04-01  2000-04-30  30       2000   4   2   April
2000-05-01  2000-05-31  31       2000   5   2   May
2000-06-01  2000-06-30  30       2000   6   2   June
2000-07-01  2000-07-31  31       2000   7   3   July
2000-08-01  2000-08-31  31       2000   8   3   August
2000-09-01  2000-09-30  30       2000   9   3   September
2000-10-01  2000-10-31  31       2000   10  4   October
2000-11-01  2000-11-30  30       2000   11  4   November
2000-12-01  2000-12-31  31       2000   12  4   December
2001-01-01  2001-01-31  31       2001   1   1   January
2001-02-01  2001-02-28  28       2001   2   1   February
2001-03-01  2001-03-31  31       2001   3   1   March

... 1,170 rows in the middle ...

2098-10-01  2098-10-31  31       2098   10  4   October
2098-11-01  2098-11-30  30       2098   11  4   November
2098-12-01  2098-12-31  31       2098   12  4   December
2099-01-01  2099-01-31  31       2099   1   1   January
2099-02-01  2099-02-28  28       2099   2   1   February
2099-03-01  2099-03-31  31       2099   3   1   March
2099-04-01  2099-04-30  30       2099   4   2   April
2099-05-01  2099-05-31  31       2099   5   2   May
2099-06-01  2099-06-30  30       2099   6   2   June
2099-07-01  2099-07-31  31       2099   7   3   July
2099-08-01  2099-08-31  31       2099   8   3   August
2099-09-01  2099-09-30  30       2099   9   3   September
2099-10-01  2099-10-31  31       2099   10  4   October
2099-11-01  2099-11-30  30       2099   11  4   November
2099-12-01  2099-12-31  31       2099   12  4   December