January 9, 2018 at 9:17 am
Hey Guys,
Happy New Year.
I have a very interesting DimDate population request.
I was intially going to manually populate this but I figured I'd ask to see if there is a way to programatically do this with TSQL and save the code for future reuse.
I have a table called DimDate
CREATE TABLE #DimDate
(
ActualDate datetime NULL,
ActualMonth varchar(15) NULL,
FiscalYear varchar(6) NULL,
HolidayName varchar(15) NULL,
HolidayFlag tinyint NULL,
WeekendName varchar(15) NULL,
WeekendFlag tinyint NULL
)
I need to populate the table with DimDates while flagging holidays and weekends. The holiday rules are as follows;
1. A holiday on a Saturday is taken on Friday
2. A holiday on a Sunday is taken on Monday.
3. Fiscal Year begins on September 1st of every year
So for example, for a Christmas holiday, the company gives 2 days off. The day of the holiday and the day after.
If Christmas is on Friday December 25 and the day after is Saturday December 26, the holiday would be Friday December 25 and Monday December 28
If Christmas is on Saturday December 25 and the day after is Sunday December 26, the holiday would be Friday December 24 and Monday December 27
If Christmas is on Sunday December 25 and the day after is Monday December 26, the holiday would be Monday December 27 and TuesdayDecember 28
After population of dates from August 29 2017 through August 31st 2018 (with some irrelevant dates skipped in between), the data in the table should look something like this;
DDL Information
CREATE TABLE #DimDate
(
ActualDate datetime NULL,
ActualMonth varchar(15) NULL,
FiscalYear varchar(6) NULL,
HolidayName varchar(15) NULL,
HolidayFlag tinyint NULL,
WeekendName varchar(15) NULL,
WeekendFlag tinyint NULL
)
INSERT INTO #DimDate (ActualDate, ActualMonth, FiscalYear, HolidayName, HolidayFlag, WeekendName, WeekendFlag)
SELECT '2017-08-29 00:00:00.000', '08', 'FY17', NULL, 0, NULL, 0 UNION ALL
SELECT '2017-08-30 00:00:00.000', '08', 'FY17', NULL, 0, NULL, 0 UNION ALL
SELECT '2017-08-31 00:00:00.000', '08', 'FY17', NULL, 0, NULL, 0 UNION ALL
SELECT '2017-09-01 00:00:00.000', '09', 'FY18', NULL, 0, NULL, 0 UNION ALL
SELECT '2017-09-02 00:00:00.000', '09', 'FY18', NULL, 0, 'Saturday', 1 UNION ALL
SELECT '2017-09-03 00:00:00.000', '09', 'FY18', NULL, 0, 'Sunday', 1 UNION ALL
SELECT '2017-09-04 00:00:00.000', '09', 'FY18', 'Labor Day', 1, NULL, 0 UNION ALL
SELECT '2017-09-05 00:00:00.000', '09', 'FY18', NULL, 0, NULL, 0 UNION ALL
SELECT '2017-09-06 00:00:00.000', '09', 'FY18', NULL, 0, NULL, 0 UNION ALL
SELECT '2017-09-07 00:00:00.000', '09', 'FY18', NULL, 0, NULL, 0 UNION ALL
SELECT '2017-09-08 00:00:00.000', '09', 'FY18', NULL, 0, NULL, 0 UNION ALL
SELECT '2017-09-09 00:00:00.000', '09', 'FY18', NULL, 0, 'Saturday', 1 UNION ALL
SELECT '2017-09-10 00:00:00.000', '09', 'FY18', NULL, 0, 'Sunday', 1 UNION ALL
SELECT '2017-09-11 00:00:00.000', '09', 'FY18', NULL, 0, NULL, 0 UNION ALL
SELECT '2017-09-12 00:00:00.000', '09', 'FY18', NULL, 0, NULL, 0 UNION ALL
--Skipped Dates
SELECT '2017-12-22 00:00:00.000', '12', 'FY18', NULL, 0, NULL, 0 UNION ALL
SELECT '2017-12-23 00:00:00.000', '12', 'FY18', NULL, 0, 'Saturday', 1 UNION ALL
SELECT '2017-12-24 00:00:00.000', '12', 'FY18', NULL, 0, 'Sunday', 1 UNION ALL
SELECT '2017-12-25 00:00:00.000', '12', 'FY18', 'Christmas', 1, NULL, 0 UNION ALL
SELECT '2017-12-26 00:00:00.000', '12', 'FY18', 'Day After', 1, NULL, 0 UNION ALL
SELECT '2017-12-27 00:00:00.000', '12', 'FY18', NULL, 0, NULL, 0 UNION ALL
SELECT '2017-12-28 00:00:00.000', '12', 'FY18', NULL, 0, NULL, 0 UNION ALL
SELECT '2017-12-29 00:00:00.000', '12', 'FY18', NULL, 0, NULL, 0 UNION ALL
SELECT '2017-12-30 00:00:00.000', '12', 'FY18', NULL, 0, 'Saturday', 1 UNION ALL
SELECT '2017-12-31 00:00:00.000', '12', 'FY18', NULL, 0, 'Sunday', 1 UNION ALL
SELECT '2018-01-01 00:00:00.000', '01', 'FY18', 'New Year', 1, NULL, 0 UNION ALL
SELECT '2018-01-02 00:00:00.000', '01', 'FY18', 'Day After', 1, NULL, 0 UNION ALL
SELECT '2018-01-03 00:00:00.000', '01', 'FY18', NULL, 0, NULL, 0 UNION ALL
SELECT '2018-01-04 00:00:00.000', '01', 'FY18', NULL, 0, NULL, 0 UNION ALL
SELECT '2018-01-05 00:00:00.000', '01', 'FY18', NULL, 0, NULL, 0 UNION ALL
SELECT '2018-01-06 00:00:00.000', '01', 'FY18', NULL, 0, 'Saturday', 1 UNION ALL
SELECT '2018-01-07 00:00:00.000', '01', 'FY18', NULL, 0, 'Sunday', 1 UNION ALL
--Skipped Dates
SELECT '2018-08-30 00:00:00.000', '08', 'FY18', NULL, 0, NULL, 0 UNION ALL
SELECT '2018-08-31 00:00:00.000', '08', 'FY18', NULL, 0, NULL, 0 UNION ALL
SELECT '2018-09-01 00:00:00.000', '09', 'FY19', NULL, 0, NULL, 0
Select * from #DimDate
I appreciate taking out any time on this request.
January 9, 2018 at 10:07 am
Hi,
Great post. I thought I'd chime in with something that hopefully could help. My organization has a similar DimDate table. Upon taking a closer look, it's populated by an SSIS package which pulls data from a staging tables, which appears to contain years. For the table design, there's a script which creates the table...I'll post a snippet from it:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StgNumbersSmall]') AND type in (N'U'))
DROP TABLE [dbo].[StgNumbersSmall]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StgNumbersBig]') AND type in (N'U'))
DROP TABLE [dbo].[StgNumbersBig]
GO
/* Create First numbers table for key generation */
CREATE TABLE dbo.StgNumbersSmall (Number INT);
INSERT INTO dbo.StgNumbersSmall
VALUES (0)
,(1)
,(2)
,(3)
,(4)
,(5)
,(6)
,(7)
,(8)
,(9);
GO
/* Create Second numbers table for key generation */
CREATE TABLE dbo.StgNumbersBig (Number_Big BIGINT);
INSERT INTO dbo.StgNumbersBig (Number_Big)
SELECT (tenthousands.number * 10000 + thousands.number * 1000 + hundreds.number * 100 + tens.number * 10 + ones.number) AS number_big
FROM dbo.StgNumbersSmall tenthousands
,dbo.StgNumbersSmall thousands
,dbo.StgNumbersSmall hundreds
,dbo.StgNumbersSmall tens
,dbo.StgNumbersSmall ones;
GO
/****** Object: Drop Table [dbo].[DimDate] Script Date: 02/17/2015 23:51:08 ******/
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_dbo_DimDate_InsertAuditKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[DimDate]'))
ALTER TABLE [dbo].[DimDate] DROP CONSTRAINT [FK_dbo_DimDate_InsertAuditKey]
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_dbo_DimDate_UpdateAuditKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[DimDate]'))
ALTER TABLE [dbo].[DimDate] DROP CONSTRAINT [FK_dbo_DimDate_UpdateAuditKey]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimDate]') AND type in (N'U'))
DROP TABLE [dbo].[DimDate]
GO
/****** Object: Table [dbo].[DimDate] Script Date: 3/3/2015 2:16:32 PM ******/
CREATE TABLE dbo.DimDate (
[DateKey] int IDENTITY NOT NULL
, [FullDate] date NOT NULL
, [MonthDayYear] nvarchar(10) NOT NULL
, [FullDateDescription] nvarchar(30) NOT NULL
, [DayName] nvarchar(10) NOT NULL
, [DayOfWeek] smallint NOT NULL
, [DayOfMonth] smallint NOT NULL
, [DayOfYear] smallint NOT NULL
, [WeekdayIndicator] nvarchar(1) NOT NULL
, [LastDayOfMonthIndicator] nvarchar(1) NOT NULL
, [WeekOfYear] smallint NOT NULL
, [MonthName] nvarchar(10) NOT NULL
, [MonthNameAbbr] nvarchar(10) NOT NULL
, [CalendarMonth] smallint NOT NULL
, [CalendarQuarter] smallint NOT NULL
, [CalendarYear] smallint NOT NULL
, [CalendarYearMonth] nvarchar(10) NOT NULL
, [CalendarYearQuarter] nvarchar(10) NOT NULL
, [FiscalMonth] smallint NOT NULL
, [FiscalQuarter] smallint NOT NULL
, [FiscalYear] smallint NOT NULL
, [FiscalYearMonth] nvarchar(10) NOT NULL
, [FiscalYearQuarter] nvarchar(10) NOT NULL
, [AccountingPeriod] smallint NOT NULL
, [AccountingYearPeriod] int NOT NULL
, [InsertAuditKey] int NOT NULL
, [UpdateAuditKey] int NOT NULL
, CONSTRAINT [PK_dbo.DimDate] PRIMARY KEY CLUSTERED
(
[DateKey] 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
--Table extended properties...
exec sys.sp_addextendedproperty @name=N'Table Type', @value=N'Dimension', @level0type=N'SCHEMA', @level0name=dbo, @level1type=N'TABLE', @level1name=DimDate
exec sys.sp_addextendedproperty @name=N'Display Name', @value=N'Date Dimension', @level0type=N'SCHEMA', @level0name=dbo, @level1type=N'TABLE', @level1name=DimDate
exec sys.sp_addextendedproperty @name=N'Table Description', @value=N'Provides date attributes for analyzing facts, such as the day of the week/year, names of days/months, etc.', @level0type=N'SCHEMA', @level0name=dbo, @level1type=N'TABLE', @level1name=DimDate
exec sys.sp_addextendedproperty @name=N'ETL Process Name', @value=N'Load_DimDate.dtsx', @level0type=N'SCHEMA', @level0name=dbo, @level1type=N'TABLE', @level1name=DimDate
;
SET IDENTITY_INSERT dbo.DimDate ON
;
INSERT INTO dbo.DimDate (DateKey, FullDate, MonthDayYear, FullDateDescription, DayName, DayOfWeek, DayOfMonth, DayOfYear, WeekdayIndicator, LastDayOfMonthIndicator, WeekOfYear, MonthName, MonthNameAbbr, CalendarMonth, CalendarQuarter, CalendarYear, CalendarYearMonth, CalendarYearQuarter, FiscalMonth, FiscalQuarter, FiscalYear, FiscalYearMonth, FiscalYearQuarter, AccountingPeriod, AccountingYearPeriod, InsertAuditKey, UpdateAuditKey)
VALUES (-1, '1900-01-01', 'Unknown', 'Unknown', 'Unknown', 0, 0, 0, 'U', 'U', 0, 'Unknown', 'Unk', 0, 0, 0, 'Unknown', 'Unknown', 0, 0, 0, 'Unknown', 'Unknown', 0, 190001, -1, -1)
;
SET IDENTITY_INSERT dbo.DimDate OFF
;
--Column extended properties
exec sys.sp_addextendedproperty @name=N'Display Name', @value=N'Date Key', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'DateKey';
exec sys.sp_addextendedproperty @name=N'Display Name', @value=N'Full Date', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'FullDate';
exec sys.sp_addextendedproperty @name=N'Display Name', @value=N'Month/Day/Year', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'MonthDayYear';
exec sys.sp_addextendedproperty @name=N'Display Name', @value=N'Full Date Description', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'FullDateDescription';
exec sys.sp_addextendedproperty @name=N'Display Name', @value=N'Day Name', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'DayName';
exec sys.sp_addextendedproperty @name=N'Display Name', @value=N'Day of Week', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'DayOfWeek';
exec sys.sp_addextendedproperty @name=N'Display Name', @value=N'Day of Month', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'DayOfMonth';
exec sys.sp_addextendedproperty @name=N'Display Name', @value=N'Day of Year', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'DayOfYear';
exec sys.sp_addextendedproperty @name=N'Display Name', @value=N'Weekday Indicator', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'WeekdayIndicator';
exec sys.sp_addextendedproperty @name=N'Display Name', @value=N'Last Day of Month Indicator', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'LastDayOfMonthIndicator';
exec sys.sp_addextendedproperty @name=N'Display Name', @value=N'Week of Year', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimDate', @level2type=N'COLUMN', @level2name=N'WeekOfYear';
As you can see, it's using a stored procedure, sp_addextendedproperty, which passes in parameters. I know this is pretty complex, but maybe there's something you'll be able to take away from it and apply to your scenario...I thought there were some commonalities which could hopefully help. Good luck 🙂
January 9, 2018 at 10:32 am
First off make all your columns NOT NULL. It is inefficient to store NULLable datatypes when all rows will have data.
The simplest way to do this is with the LAG and LEAD function, which allows you to see the day before and day after. You know each holiday date and how many days of vacation to give. Code your UPDATE statement (or statements if you can't figure out how to do it in one pass) and use LAG/LEAD and CASE and DATEPART(weekday..) to update the proper rows.
Note that the DATEFIRST setting comes into play here, as does other things such as time zones if you have those in play.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply