January 9, 2018 at 9:16 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 9:53 am
Duplicate Post?
...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply