How to add more date data to a Date Dimension table?-CodePudding

I am currently trying to figure out a MSSQL database.

There is a date dimension table being used by a lot of reports, and I can see that the dates stop on 2nd January 2022. So basically it is a ticking time bomb and I need to add another few years onto it.

Whoever created this date dimension didn't go any further than 2nd Jan 2022.

I would like to add another 5 or 10 years to it. Is this possible to do within SSMS? Or would it be better to create the table in Excel and then import and replace the current date dimension?

Below is an example of some of the columns in this date dimension table:

What is the best way to go around adding extra years to this? SQL Server v10.5

Table Structure:

USE [MPH_DWH_Cork_Activity]
GO

/****** Object:  Table [dbo].[bi_dim_date]    Script Date: 11/9/2021 3:06:49 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[bi_dim_date](
    [DateKey] [datetime] NULL,
    [DateInt] [int] NULL,
    [YearKey] [int] NULL,
    [QuarterOfYear] [int] NULL,
    [MPH_MonthOfYear] [int] NULL,
    [MonthOfYear] [int] NULL,
    [DayOfMonth] [int] NULL,
    [MonthName] [varchar](16) NULL,
    [MonthInCalendar] [datetime] NULL,
    [QuarterInCalendar] [varchar](16) NULL,
    [DayOfWeekName] [varchar](16) NULL,
    [DayInWeek] [int] NULL,
    [Week Num] [int] NULL,
    [DateKey1] [datetime] NOT NULL,
    [Year] [int] NULL,
    [YearID] [int] NULL,
    [WeekID] [int] NULL,
    [First Date in Rolling 4 Week Period] [datetime] NULL,
    [Last Date in Rolling 4 Week Period] [datetime] NULL,
 CONSTRAINT [PK__bi_dim_d__6690D80A2A4B4B5E] PRIMARY KEY CLUSTERED 
(
    [DateKey1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[bi_dim_date] ADD  CONSTRAINT [DF__bi_dim_da__DateI__22401542]  DEFAULT (NULL) FOR [DateInt]
GO

ALTER TABLE [dbo].[bi_dim_date] ADD  CONSTRAINT [DF__bi_dim_da__YearK__2334397B]  DEFAULT (NULL) FOR [YearKey]
GO

ALTER TABLE [dbo].[bi_dim_date] ADD  CONSTRAINT [DF__bi_dim_da__Quart__24285DB4]  DEFAULT (NULL) FOR [QuarterOfYear]
GO

ALTER TABLE [dbo].[bi_dim_date] ADD  CONSTRAINT [DF__bi_dim_da__Month__251C81ED]  DEFAULT (NULL) FOR [MonthOfYear]
GO

ALTER TABLE [dbo].[bi_dim_date] ADD  CONSTRAINT [DF__bi_dim_da__DayOf__2610A626]  DEFAULT (NULL) FOR [DayOfMonth]
GO

ALTER TABLE [dbo].[bi_dim_date] ADD  CONSTRAINT [DF__bi_dim_da__Month__2704CA5F]  DEFAULT (NULL) FOR [MonthName]
GO

ALTER TABLE [dbo].[bi_dim_date] ADD  CONSTRAINT [DF__bi_dim_da__Quart__28ED12D1]  DEFAULT (NULL) FOR [QuarterInCalendar]
GO

ALTER TABLE [dbo].[bi_dim_date] ADD  CONSTRAINT [DF__bi_dim_da__DayOf__29E1370A]  DEFAULT (NULL) FOR [DayOfWeekName]
GO

ALTER TABLE [dbo].[bi_dim_date] ADD  CONSTRAINT [DF__bi_dim_da__DayIn__2AD55B43]  DEFAULT (NULL) FOR [DayInWeek]
GO

ALTER TABLE [dbo].[bi_dim_date] ADD  CONSTRAINT [DF__bi_dim_da__Week __2BC97F7C]  DEFAULT (NULL) FOR [Week Num]
GO

ALTER TABLE [dbo].[bi_dim_date] ADD  CONSTRAINT [DF__bi_dim_dat__Year__2CBDA3B5]  DEFAULT (NULL) FOR [Year]
GO

ALTER TABLE [dbo].[bi_dim_date] ADD  CONSTRAINT [DF__bi_dim_da__YearI__2DB1C7EE]  DEFAULT (NULL) FOR [YearID]
GO

ALTER TABLE [dbo].[bi_dim_date] ADD  CONSTRAINT [DF__bi_dim_da__WeekI__2EA5EC27]  DEFAULT (NULL) FOR [WeekID]
GO

CodePudding user response：

It is really a small matter via an ad-hoc tally table. Without the actual table structure, we have no idea as to data types and whether or not the columns are calculated.

That said, here is a quick example

Declare @Date1 date = '2022-01-02'
Declare @Date2 date = '2030-12-31'

Select DateKey = D
      ,DateInt = convert(int,convert(varchar(8),D,112))
      ,YearKey = datepart(year,D)
      ,QuarterOfYear = datepart(quarter,D)
      ,MPH_MonthOfYear = '???'
      ,MonthOfYear = datepart(month,D)
      ,DayofMonth  = datepart(day,D)
      ,MonthName   = datename(month,D)
 From (
        Select Top (DateDiff(DAY,@Date1,@Date2) 1) D=DateAdd(DAY,-1 Row_Number() Over (Order By (Select Null)),@Date1) 
         From  master..spt_values n1,master..spt_values n2
      ) src

Results

CodePudding user response：

The easiest is to modify the original script to run a new period If you don't have it it will take you some time to rebuild it Fortunately you seen that before December 30

Beware of special rules about 1st and last week of the year Identify the exact rule you have to follow, it can be tricky I don't think excel will be a great help, it can have other rules than the one you want to follow