Dennis and Jim's Data Engineering Blog: A Date Dimension Starter Kit for your Data Warehouse

When building a data warehouse one of the most important dimensions to create, if not your most important, is your dimension that manages time. The most common of these is at the granularity of a day. This would constitute a record for each day with attributes that provide meaning to the day. The Kimball Group provides a very good spreadsheet that helps with creating and populating a time dimension for your project. However; there are not that many fields, does nothing with day light savings time, and if you have a very large time span to deal with, can be a little cumbersome. In this entry, I provide an alternative to this along, with meta data and helper methods to get your data warehouse project started.

At the end of this entry I provide a script that will essentially create the following (it's in transact-sql, so it will only work for SQL Server):

DIM_DAY

Column Name	Datatype	Definition
DAY_KEY	int	This is the primary key of dim_day dimension. It is a surrogate key generated at script run time
FULL_DATE	datetime	The full date value for the day
DAY_OF_WEEK	tinyint	The day of the week for the full date
DAY_NUM_IN_MONTH	tinyint	Day number in the month is an integer representing the calendar day number for the full date.
DAY_NUM_OVERALL	smallint	Day_number_over_all is the sequential number for the day in the dim_day table.
DAY_NAME	nvarchar(10)	Day_Name is the day of the week name (Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday)
DAY_ABBREV	nchar(10)	Day_Abbrev is the three letter abbreviation for the day of the week(Mon, Tue, Wed, Thu, Fri, Sat, Sun)
WEEKDAY_FLAG	nchar(10)	Weekday_Flag is a true false flag represented by 'y' orr 'n' that denotes that the full date day is a week day or not.
WEEK_NUM_IN_YEAR	tinyint	Week_Num_In_Year is an integer representing the sequential number of the week during the given year in full_date. (1-52) sometimes as many as 54 in leap year.
WEEK_NUM_OVERALL	smallint	Week_number_over_all is the sequential number for the week in the dim_day table.
WEEK_BEGIN_DATE	smalldatetime	Week_begin_dsate_key is the full date value for the begining day of the week for the full date.
MONTH	tinyint	Month is an integer representing the month of the year (1-12)
MONTH_NUM_OVERALL	smallint	Week_number_over_all is the sequential number for the week in the dim_day table.
MONTH_NAME	nvarchar(10)	Month_name is the text name of the month of the year in the full date (January, February, March, April, May, June, July, August, September, October, November, December)
MONTH_ABBREV	nchar(10)	Month_Abbrev is the three letter abbreviation of the month of the year text (Jan. Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec)
QUARTER	tinyint	Quarter is the quarter of the year for the full date (1-4)
YEAR	smallint	Year is the integer value representing the year in the full date.
YEARMO	int	Yearmo is the integer value of the year and month in yyyymm format.
FISCAL_MONTH	tinyint	Fscal_month is the six month offset of the calendar month for ther full date (1 = 7, 2 = 8, 3 = 9, etc.)
FISCAL_QUARTER	tinyint	Fiscal_quarter is the quarter of the fiscal year offset by 2 from the calendar quarter (1 = 3, 2 = 4, 3 = 1, 4 = 2)
FISCAL_YEAR	smallint	Fiscal yesar is the offset year that begins in July instead of January such that for July 2010 is fiscal year 2011 and June 2011 is still ficsal year 2011.
LAST_DAY_IN_MONTH_FLAG	nchar(10)	Last_Day_In_Month_Flag is a true false represented by 'y' or 'n' denoting that the full date is the last day in the month.
SAME_WEEKDAY_YEAR_AGO_DATE	smalldatetime	Same_week_day_year_ago_date is the full date of the same weekday for the previous year.
FULL_DATE_START_TIME	datetime	This is the full date value for the date at midnight in datetime datatype.
FULL_DATE_END_TIME	datetime	This is the datetime for the end of the day 11:59 in datetime datatype.
MINUTES_IN_MONTH	int	This is the total number of minutes in the month that the date is in.
PREVIOUS_MONTH_PREVIOUS_YEAR_FIRST_DAY	datetime	The first of the month for the previous month of the previous year example: today is 3/22/2011, this field would be 2/1/2010
CURRENT_MONTH_PREVIOUS_YEAR_FIRST_DAY	datetime	The first of the month for the current month of the previous year example: today is 3/22/2011, this field would be 3/1/2010)
DATE_PREVIOUS_MONTH	datetime	The same date last month. Example today is 3/4/2011, this field would be set to 2/4/2011.
CURRENT_MONTH_FIRST_DAY	datetime	This is the first of the month of the current month. Ex the date is 5/8/2011, this field would be set to 5/1/2011.
DATE_PREVIOUS_MONTH_PREVIOUS_YEAR	datetime	The same date last month for the current month in the previous year. Example the date is set to 6/12/2011, this field would be set to 5/12/2010.
DAYS_INTO_YEAR	smallint	The number of days into the year this date is.
DAYS_INTO_QUARTER	smallint	The number of days into the quarter this date is.
DAYS_INTO_FISCAL_YEAR	smallint	The number of days into the fiscal year this date is.
DAYS_INTO_FISCAL_QUARTER	smallint	The number of days into the fiscal quarter this date is.
DAYS_IN_FISCAL_QUARTER	smallint	The number of days in the fiscal quarter.
DAYS_IN_FISCAL_YEAR	smallint	The number of days in the fiscal year.
DAYS_IN_QUARTER	smallint	The number of days in the quarter.
DAYS_IN_YEAR	smallint	The number of days in the year.
PERCENT_COMPLETION _FISCAL_YEAR	decimal(10, 2)	This is the decimal value of days into fiscal year/ total fiscal year days.
PERCENT_COMPLETION_FISCAL_QUARTER	decimal(10, 2)	This is the decimal value of days into fiscal quarter / total fiscal quarter days.
PERCENT_COMPLETION_YEAR	decimal(10, 2)	This is the decimal value of days into year/ total year days.
PERCENT_COMPLETION_QUARTER	decimal(10, 2)	This is the decimal value of days into quarter/ total quarter days.
FIRST_DAY_CURRENT_YEAR	datetime	First day of the year this date is in.
FIRST_DAY_PREVIOUS_YEAR	datetime	First day of the year for the previous year this date is in.
FIRST_DAY_CURRENT_QUARTER	datetime	The first day of the quarter this date is in.
FIRST_DAY_CURRENT_QUARTER_PREVIOUS_YEAR	datetime	First day of the current quarter for the previous year this date is in.
FIRST_DAY_PREVIOUS_QUARTER	datetime	The date of the first day of the previous quarter that this date is in.
FIRST_DAY_PREVIOUS_QUARTER_PREVIOUS_YEAR	datetime	The first day of the previous quarter of the previous year this date is in.
CURRENT_MONTH_FIRST_OF_MONTH	datetime	The date of the first of the month that this date is in.
CURRENT_MONTH_LAST_OF_MONTH	datetime	The date of the last day of the month this date is in at 23:59:59.000.
PREVIOUS_1MONTH_FIRST_OF MONTH	datetime	This is the first of the month of the previous month the current date is in.
PREVIOUS_1MONTH_LAST_OF MONTH	datetime	This is the last of the month of the previous month the current date is in at 23:59:59.000.
PREVIOUS_2MONTH_FIRST_OF MONTH	datetime	This is the first of the month 2 months ago that the current date is in.
PREVIOUS_2MONTH_LAST_OF MONTH	datetime	This is the last of the month 2 months ago that the current date is in at 23:59:59.000.
PREVIOUS_3MONTH_FIRST_OF MONTH	datetime	This is the first of the month 3 months ago that the current date is in.
PREVIOUS_3MONTH_LAST_OF MONTH	datetime	This is the last of the month 3 months ago that the current date is in at 23:59:59.000.
PREVIOUS_4MONTH_FIRST_OF MONTH	datetime	This is the first of the month 4 months ago that the current date is in.
PREVIOUS_4MONTH_LAST_OF MONTH	datetime	This is the last of the month 4 months ago that the current date is in at 23:59:59.000.
PREVIOUS_5MONTH_FIRST_OF MONTH	datetime	This is the first of the month 5 months ago that the current date is in.
PREVIOUS_5MONTH_LAST_OF MONTH	datetime	This is the last of the month 5 months ago that the current date is in at 23:59:59.000.
PREVIOUS_6MONTH_FIRST_OF MONTH	datetime	This is the first of the month 6 months ago that the current date is in.
PREVIOUS_6MONTH_LAST_OF MONTH	datetime	This is the last of the month 6 months ago that the current date is in at 23:59:59.000.
PREVIOUS_7MONTH_FIRST_OF MONTH	datetime	This is the first of the month 7 months ago that the current date is in.
PREVIOUS_7MONTH_LAST_OF MONTH	datetime	This is the last of the month 7 months ago that the current date is in at 23:59:59.000.
PREVIOUS_8MONTH_FIRST_OF MONTH	datetime	This is the first of the month 8 months ago that the current date is in.
PREVIOUS_8MONTH_LAST_OF MONTH	datetime	This is the last of the month 8 months ago that the current date is in at 23:59:59.000.
PREVIOUS_9MONTH_FIRST_OF MONTH	datetime	This is the first of the month 9 months ago that the current date is in.
PREVIOUS_9MONTH_LAST_OF MONTH	datetime	This is the last of the month 9 months ago that the current date is in at 23:59:59.000.
PREVIOUS_10MONTH_FIRST_OF MONTH	datetime	This is the first of the month 10 months ago that the current date is in.
PREVIOUS_10MONTH_LAST_OF MONTH	datetime	This is the last of the month 10 months ago that the current date is in at 23:59:59.000.
PREVIOUS_11MONTH_FIRST_OF MONTH	datetime	This is the first of the month 11 months ago that the current date is in.
PREVIOUS_11MONTH_LAST_OF MONTH	datetime	This is the last of the month 11 months ago that the current date is in at 23:59:59.000.
PREVIOUS_12MONTH_FIRST_OF MONTH	datetime	This is the first of the month 12 months ago that the current date is in.
PREVIOUS_12MONTH_LAST_OF MONTH	datetime	This is the last of the month 12 months ago that the current date is in at 23:59:59.000.
PREVIOUS_13MONTH_FIRST_OF MONTH	datetime	This is the first of the month 13 months ago that the current date is in.
PREVIOUS_13MONTH_LAST_OF MONTH	datetime	This is the last of the month 13 months ago that the current date is in at 23:59:59.000.
PREVIOUS_14MONTH_FIRST_OF MONTH	datetime	This is the first of the month 14 months ago that the current date is in.
PREVIOUS_14MONTH_LAST_OF MONTH	datetime	This is the last of the month 14 months ago that the current date is in at 23:59:59.000.
PREVIOUS_15MONTH_FIRST_OF MONTH	datetime	This is the first of the month 15 months ago that the current date is in.
PREVIOUS_15MONTH_LAST_OF MONTH	datetime	This is the last of the month 15 months ago that the current date is in at 23:59:59.000.
PREVIOUS_16MONTH_FIRST_OF MONTH	datetime	This is the first of the month 16 months ago that the current date is in.
PREVIOUS_16MONTH_LAST_OF MONTH	datetime	This is the last of the month 16 months ago that the current date is in at 23:59:59.000.
PREVIOUS_17MONTH_FIRST_OF MONTH	datetime	This is the first of the month 17 months ago that the current date is in.
PREVIOUS_17MONTH_LAST_OF MONTH	datetime	This is the last of the month 17 months ago that the current date is in at 23:59:59.000.
PREVIOUS_18MONTH_FIRST_OF MONTH	datetime	This is the first of the month 18 months ago that the current date is in.
PREVIOUS_18MONTH_LAST_OF MONTH	datetime	This is the last of the month 18 months ago that the current date is in at 23:59:59.000.
PREVIOUS_19MONTH_FIRST_OF MONTH	datetime	This is the first of the month 19 months ago that the current date is in.
PREVIOUS_19MONTH_LAST_OF MONTH	datetime	This is the last of the month 19 months ago that the current date is in at 23:59:59.000.
PREVIOUS_20MONTH_FIRST_OF MONTH	datetime	This is the first of the month 20 months ago that the current date is in.
PREVIOUS_20MONTH_LAST_OF MONTH	datetime	This is the last of the month 20 months ago that the current date is in at 23:59:59.000.
PREVIOUS_21MONTH_FIRST_OF MONTH	datetime	This is the first of the month 21 months ago that the current date is in.
PREVIOUS_21MONTH_LAST_OF MONTH	datetime	This is the last of the month 21 months ago that the current date is in at 23:59:59.000.
PREVIOUS_22MONTH_FIRST_OF MONTH	datetime	This is the first of the month 22 months ago that the current date is in.
PREVIOUS_22MONTH_LAST_OF MONTH	datetime	This is the last of the month 22 months ago that the current date is in at 23:59:59.000.
PREVIOUS_23MONTH_FIRST_OF MONTH	datetime	This is the first of the month 23 months ago that the current date is in.
PREVIOUS_23MONTH_LAST_OF MONTH	datetime	This is the last of the month 23 months ago that the current date is in at 23:59:59.000.
PREVIOUS_24MONTH_FIRST_OF MONTH	datetime	This is the first of the month 24 months ago that the current date is in.
PREVIOUS_24MONTH_LAST_OF MONTH	datetime	This is the last of the month 24 months ago that the current date is in at 23:59:59.000.
FIRST_DATE_IN_MONTH_FLG	nchar(10)	This flag determines whether or not this date is the first date of the month it is in.
YYYY_MM_DD	nchar(10)	This is the nchar(8) value of the date formatted to YYYYMMDD. This is a common format coming from SAP BW.
CURRENT_DATE_FLG	nchar(10)	This flag determines whether or not this date is the current date.
LAST_MOD_DATE	datetime	Last_Mod_Date is the date and time that the record was loaded to the Dimension Table.
FIRST_QUARTER_FIRST_MONTH_FIRST_DAY	datetime	This is the first day of the first month of the first quarter of the year.
FIRST_QUARTER_SECOND_MONTH_FIRST_DAY	datetime	This is the first day of the second month of the first quarter of the year.
FIRST_QUARTER_THIRD_MONTH_FIRST_DAY	datetime	This is the first day of the third month of the first quarter of the year.
SECOND_QUARTER_FIRST_MONTH_FIRST_DAY	datetime	This is the first day of the first month of the second quarter of the year.
SECOND_QUARTER_SECOND_MONTH_FIRST_DAY	datetime	This is the first day of the second month of the second quarter of the year.
SECOND_QUARTER_THIRD_MONTH_FIRST_DAY	datetime	This is the first day of the third month of the second quarter of the year.
THIRD_QUARTER_FIRST_MONTH_FIRST_DAY	datetime	This is the first day of the first month of the third quarter of the year.
THIRD_QUARTER_SECOND_MONTH_FIRST_DAY	datetime	This is the first day of the second month of the third quarter of the year.
THIRD_QUARTER_THIRD_MONTH_FIRST_DAY	datetime	This is the first day of the third month of the third quarter of the year.
FOURTH_QUARTER_FIRST_MONTH_FIRST_DAY	datetime	This is the first day of the first month of the fourth quarter of the year.
FOURTH_QUARTER_SECOND_MONTH_FIRST_DAY	datetime	This is the first day of the second month of the fourth quarter of the year.
FOURTH_QUARTER_THIRD_MONTH_FIRST_DAY	datetime	This is the first day of the third month of the fourth quarter of the year.
PACIFIC_STANDARD_TIME_OFFSET	smallint	The hour difference between GMT and pacific standard time for the given day.
EASTERN_STANDARD_TIME_OFFSET	smallint	The hour difference between GMT and eastern standard time for the given day.
CENTRAL_STANDARD_TIME_OFFSET	smallint	The hour difference between GMT and central standard time for the given day.
MOUNTAIN_STANDARD_TIME_OFFSET	smallint	The hour difference between GMT and mountain standard time for the given day.
HAWAII_TIME_OFFSET	smallint	The hour difference between GMT and hawaii time for the given day.
ARIZONA_TIME_OFFSET	smallint	The hour difference between GMT and arizona time for the given day.
ALASKA_TIME_OFFSET	smallint	The hour difference between GMT and alaska time for the given day.

A time dimension at the level of granularity of a month is also created. If you do not need this for your project you can delete after the script is finished, but is needed for the creation of your day dimension.

DIM_MONTH

Column Name	Datatype	Definition
MONTH_KEY	int	This is the primary surrogate key for this dimension.
YEARMO	int	yearmo is the integer value of the year and month in yyyymm format.
NUM_WORK_DAYS	int	The number of days in a month available for work. The number of days M-F subtracting holidays.
NUM_HOLIDAYS	int	The number of holidays in a month.
NUM_DAYS	int	The number of days in a month total.
NUM_MINUTES	int	The total number of minutes in a month. 1440 * number of days in the month.
LAST_MOD_DATE	datetime	The last time this record was modified.
MONTH_NAME	nvarchar(25)	This is the full name of the month
MONTH_ABRV_NAME	nchar(3)	The 3 character abbreviation for the month.
YEAR	smallint	The 4 digit year of the month.
MONTH_NUMBER	smallint	The month number (ex. Feb=2, Nov=11).
QUARTER	smallint	The digit for the quarter of the year.
FISCAL_QUARTER	smallint	The digit for the fiscal quarter of the year
FISCAL_YEAR	smallint	The 4 digit value for the fiscal year of the month
FIRST_DATE_IN_MONTH	datetime	The datetime value for the first date in this month.
LAST_DATE_IN_MONTH	datetime	The datetime value for the last date in this month.
CURRENT_MONTH_FLG	nchar(1)	This flag determines whether or not this month is the current month.

In addition to these 2 tables I've included a number of helper functions to help with all kind of date related calculations. If you do not need this for your project you can delete these after the script is finished, but are needed for the creation of your day dimension. These include:

fn_RemoveTime

A function that removes the time part of a datetime passed to it, essentially sets the time to midnight

fn_GetWorkingDays

Gets the number of work days between 2 datetimes passed to it

fn_GetStartOfWeek

Gets the datetime of the first day of the week of the datetime passed to it

fn_GetLastMonthFirstDay

Gets the first day of the month previous to the month of the datetime passed to it

fn_GetLastMonthLastDay

Gets the last day of the month previous to the month of the datetime passed to it

fn_GetCurrentMonthFirstDay

Gets the first day of the month of the month of the datetime passed to it

fn_GetCurrentMonthLastDay

Gets the last day of the month of the month of the datetime passed to it

fn_GetCurrentDateYYYYMMDD

Gets the year month and day of the datetime passed to it in an YYYYMMDD

fn_GetFiscalYear

Gets the fiscal year of the datetime passed to it based on the start month of the fiscal year passed

fn_GetFiscalMonth

Gets the fiscal month of the datetime passed to it based on the start month of the fiscal year passed

fn_GetFiscalQuarter

Gets the fiscal quarter of the datetime passed to it based on the start month of the fiscal month passed

fn_GetStartOfFiscalQuarter

Gets the start datetime of the fiscal quarter of the datetime passed to it based on the start month of the fiscal year passed

fn_GetEndOfFiscalQuarter

Gets the end datetime of the fiscal quarter of the datetime passed to it based on the start month of the fiscal year passed

fn_GetStartOfFiscalYear

Gets the datetime of the start of the fiscal year of the datetime passed to it based on the start month of the fiscal year passed

fn_GetEndOfFiscalYear

Gets the datetime of the end of the fiscal year of the datetime passed to it based on the start month of the fiscal year passed

During the execution of this script another configuration table is created to capture the beginning date and ending date for each year's day light savings time. Since this is based on acts of congress, its something that can't be put into any kind of function logic. So hopefully the CFG_DAY_LIGHT_SAVINGS_TIME table created can somehow be incorporated into your ETL strategy and once congress passes the next series, can be added to this table. It currently runs until 2025. This is key in populating the time zone offset columns in dim day. For years with no day light savings time recorded, these fields will be null.

CFG_DAY_LIGHT_SAVINGS_TIME

Column Name	Datatype	Definition
BEGIN_DATE	datetime	This is the beginning date range for daylight savings time for the given year.
END_DATE	datetime	This is the end date range for daylight savings time for the given year.
YEAR_DATE	smallint	This is the year the day light savings range is relevant.

Configuring the execution of the script VERY IMPORTANT!:

Before executing the script there are a few values you need to configure. Look for these 5 lines (3 apply to DIM_DAY the other 2 apply to DIM_MONTH).

/** Configurations UPDATE THESE VALUES FOR DIM_DAY **/

-->SETS THE YEAR TO START FROM

SET @YEAR = 1967

-->SETS THE YEAR TO GO TO

SET @YEAR_LIMIT = 3000

-->SETS THE BEGINNING MONTH OF THE FISCAL YEAR (In this example 7 or July)

SET @FISCAL_MONTH_START=7

/** Configurations UPDATE THESE VALUES FOR DIM_MONTH **/

-->SETS THE YEAR TO START FROM

SET @YEAR = 1967

-->SETS THE YEAR TO GO TO

SET @YEAR_LIMIT = 3000

The first 3 lines configure the date range of your day dimension, they are hard-coded from the year 1967 to the year 3000. Also the month start of the fiscal year, this is hard-coded to 7. The last 2 apply to the year range for the month dimension. The year range MUST match the year range of the day dimension to work properly. I just ran this script as is and it took around 27 minutes to complete for years 1967-3000. Kind of a long time, still shorter than doing it manually, hopefully will only need to run this once ever.

There are 2 fields you need to keep updated in each table. DIM_DAY.CURRENT_DATE_FLG and DIM_MONTH.CURRENT_MONTH_FLG. These will have to be updated during your ETL jobs to keep up to date.

The creation and population script:

/** Function Declarations**/

-->Function to remove time from a datetime

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_RemoveTime]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[fn_RemoveTime]

CREATE FUNCTION [dbo].[fn_RemoveTime]

(@DATE DATETIME)

RETURNS DATETIME

BEGIN

DECLARE @STRIPDATE DATETIME

SET @STRIPDATE =DATEADD(dd, DATEDIFF(dd,0,@DATE), 0)

RETURN @STRIPDATE

END

-->Function to get the number of working days in a time frame

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetWorkingDays]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[fn_GetWorkingDays]

CREATE FUNCTION [dbo].[fn_GetWorkingDays]

(@startDate DATETIME, @endDate DATETIME)

RETURNS INT

BEGIN

DECLARE @range INT;

SET @range = DATEDIFF(DAY, @startDate, @endDate)+1;

RETURN

(

SELECT

@range / 7 * 5 + @range % 7 -

(

SELECT COUNT(*)

FROM

(

SELECT 1 AS d

UNION ALL SELECT 2

UNION ALL SELECT 3

UNION ALL SELECT 4

UNION ALL SELECT 5

UNION ALL SELECT 6

UNION ALL SELECT 7

) weekdays

WHERE d <= @range % 7

AND DATENAME(WEEKDAY, @endDate - d + 1)

(

'Saturday',

'Sunday'

)

);

END

-->Function to get the first day of the week of date passed

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetStartOfWeek]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[fn_GetStartOfWeek]

CREATE FUNCTION [dbo].[fn_GetStartOfWeek]

(@DATE DATETIME, @WEEK_START_DAY INT=1)

RETURNS DATETIME

BEGIN

DECLARE @START_OF_WEEK_DATE DATETIME

DECLARE @FIRST_BOW DATETIME

IF @WEEK_START_DAY BETWEEN 1 AND 7

BEGIN

SELECT @FIRST_BOW = CONVERT(DATETIME,-53690+((@WEEK_START_DAY+5)%7))

IF @DATE >= @FIRST_BOW

BEGIN

SELECT @START_OF_WEEK_DATE =

DATEADD(dd,(DATEDIFF(dd,@FIRST_BOW,@DATE)/7)*7,@FIRST_BOW)

END

RETURN @START_OF_WEEK_DATE

END

-->Function to get the first day from the previous month

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetLastMonthFirstDay]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[fn_GetLastMonthFirstDay]

CREATE FUNCTION [dbo].[fn_GetLastMonthFirstDay]

(@Date DATETIME)

RETURNS DATETIME

BEGIN

DECLARE @range DATETIME;

Select @range = (dateadd(month, datediff(month, -1, @Date) - 2, -1) + 1)

RETURN @range

END

-->Function to get the last day from the previous month

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetLastMonthLastDay]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[fn_GetLastMonthLastDay]

CREATE FUNCTION [dbo].[fn_GetLastMonthLastDay]

(@Date DATETIME)

RETURNS DATETIME

BEGIN

DECLARE @range DATETIME;

Select @range = dateadd(month, datediff(month, -1, @Date) - 1, -1)

RETURN @range

END

-->Function to get the first day of the month of the date passed

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetCurrentMonthFirstDay]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[fn_GetCurrentMonthFirstDay]

CREATE FUNCTION [dbo].[fn_GetCurrentMonthFirstDay]

(@Date DATETIME)

RETURNS DATETIME

BEGIN

DECLARE @range DATETIME;

Select @range = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@Date)-1),@Date),101)

RETURN @range

END

-->Function to get the last day of the month of the date passed

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetCurrentMonthLastDay]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[fn_GetCurrentMonthLastDay]

CREATE FUNCTION [dbo].[fn_GetCurrentMonthLastDay]

(@Date DATETIME)

RETURNS DATETIME

BEGIN

DECLARE @range DATETIME;

Select @range = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@Date))),DATEADD(mm,1,@Date)),101)

RETURN @range

END

-->Function to get the YYYYMMDD from a date passed

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetCurrentDateYYYYMMDD]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[fn_GetCurrentDateYYYYMMDD]

Create FUNCTION [dbo].[fn_GetCurrentDateYYYYMMDD]

(@Date DATETIME)

RETURNS NCHAR(8)

BEGIN

DECLARE @range NCHAR(8);

Select @range = LEFT(REPLACE(CONVERT(NVARCHAR(10), DATEADD(MM,0,@Date),20),'-',''),6)+ case when LEN(convert(nvarchar(2),DATEpart(DD,@Date)))<2 then'0'+convert(nvarchar(2),DATEpart(DD,@Date))

else convert(nvarchar(2),DATEpart(DD,@Date)) end

RETURN @range

END

-->Function to get fiscal year for date passed and fiscal year start month

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetFiscalYear]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[fn_GetFiscalYear]

CREATE FUNCTION dbo.fn_GetFiscalYear(

@date DATETIME,

@fiscalYearStartMonth int

)

RETURNS INT

BEGIN

Declare @Answer int

IF ( MONTH(@date) < @fiscalYearStartMonth)

SET @Answer = YEAR(@date) - 1

ELSE

SET @Answer = YEAR(@date)

return @Answer

END

-->Function to get fiscal month for date passed and fiscal year start month

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetFiscalMonth]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[fn_GetFiscalMonth]

CREATE FUNCTION dbo.fn_GetFiscalMonth(

@date DATETIME,

@fiscalYearStartMonth int

)

RETURNS INT

BEGIN

Declare @Answer int

IF ( MONTH(@date) > @fiscalYearStartMonth )

SET @Answer = (MONTH(@date)-@fiscalYearStartMonth)+1

ELSE IF ( MONTH(@date) = @fiscalYearStartMonth )

SET @Answer =1

ELSE IF ( MONTH(@date) < @fiscalYearStartMonth )

SET @Answer = 12-(@fiscalYearStartMonth- MONTH(@date)-1)

return @Answer

END

-->Function to get fiscal quarter for date passed and fiscal year start month

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetFiscalQuarter]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[fn_GetFiscalQuarter]

CREATE FUNCTION dbo.fn_GetFiscalQuarter(

@date DATETIME,

@fiscalYearStartMonth int

)

RETURNS INT

BEGIN

Declare @Answer int

IF ( MONTH(@date) > @fiscalYearStartMonth )

SET @Answer = (MONTH(@date)-@fiscalYearStartMonth)+1

ELSE IF ( MONTH(@date) = @fiscalYearStartMonth )

SET @Answer =1

ELSE IF ( MONTH(@date) < @fiscalYearStartMonth )

SET @Answer = 12-(@fiscalYearStartMonth- MONTH(@date)-1)

select @answer =case when @answer between 1 and 3 then 1

when @answer between 4 and 6 then 2

when @answer between 7 and 9 then 3

when @answer between 10 and 12 then 4

end

return @answer

END

-->Function to get the first date of the fiscal quarter of the date passed and fiscal year start month

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetStartOfFiscalQuarter]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[fn_GetStartOfFiscalQuarter]

CREATE FUNCTION dbo.fn_GetStartOfFiscalQuarter(

@date DATETIME,

@fiscalYearStartMonth int

)

RETURNS DATETIME

BEGIN

DECLARE @beginOfMonth DATETIME

DECLARE @fmonth INT

DECLARE @answer datetime

set @beginOfMonth = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@date)-1),@date),101)

IF ( MONTH(@date) > @fiscalYearStartMonth )

SET @fmonth = (MONTH(@date)-@fiscalYearStartMonth)+1

ELSE IF ( MONTH(@date) = @fiscalYearStartMonth )

SET @fmonth =1

ELSE IF ( MONTH(@date) < @fiscalYearStartMonth )

SET @fmonth = 12-(@fiscalYearStartMonth- MONTH(@date)-1)

select @answer =case when @fmonth in (1 ,4,7,10) then @beginOfMonth

when @fmonth in (2 ,5,8,11) then dateadd(mm,-1,@beginOfMonth)

when @fmonth in (3 ,6,9,12) then dateadd(mm,-2,@beginOfMonth)

end

return @answer

end

-->Function to get the end date of the fiscal quarter of the date passed and fiscal year start month

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetEndOfFiscalQuarter]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[fn_GetEndOfFiscalQuarter]

CREATE FUNCTION dbo.fn_GetEndOfFiscalQuarter(

@date DATETIME,

@fiscalYearStartMonth int

)

RETURNS DATETIME

BEGIN

DECLARE @beginOfMonth DATETIME

DECLARE @fmonth INT

DECLARE @answer datetime

set @beginOfMonth = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@date)-1),@date),101)

IF ( MONTH(@date) > @fiscalYearStartMonth )

SET @fmonth = (MONTH(@date)-@fiscalYearStartMonth)+1

ELSE IF ( MONTH(@date) = @fiscalYearStartMonth )

SET @fmonth =1

ELSE IF ( MONTH(@date) < @fiscalYearStartMonth )

SET @fmonth = 12-(@fiscalYearStartMonth- MONTH(@date)-1)

select @answer =case when @fmonth in (1 ,4,7,10) then dateadd(mm,+2,@beginOfMonth)

when @fmonth in (2 ,5,8,11) then dateadd(mm,+1,@beginOfMonth)

when @fmonth in (3 ,6,9,12) then @beginOfMonth

end

return CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@answer))),DATEADD(mm,1,@answer)),101)

end

-->Function to get the first date of the fiscal year of the date passed and fiscal year start month

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetStartOfFiscalYear]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[fn_GetStartOfFiscalYear]

CREATE FUNCTION dbo.fn_GetStartOfFiscalYear(

@date DATETIME,

@fiscalYearStartMonth int

)

RETURNS DATETIME

BEGIN

DECLARE @answer datetime

IF ( MONTH(@date) > @fiscalYearStartMonth )

set @answer= convert(datetime,convert(varchar,@fiscalYearStartMonth )+'-1-'+convert(varchar, datepart(year,@date) ) )

ELSE

set @answer= convert(datetime, convert(varchar,@fiscalYearStartMonth )+'-1-'+convert(varchar, datepart(year,@date)-1 ) )

return @answer

end

-->Function to get the last date of the fiscal year of the date passed and fiscal year start month

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetEndOfFiscalYear]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[fn_GetEndOfFiscalYear]

CREATE FUNCTION dbo.fn_GetEndOfFiscalYear(

@date DATETIME,

@fiscalYearStartMonth int

)

RETURNS DATETIME

BEGIN

declare @inter datetime

IF ( @fiscalYearStartMonth=1 )

set @inter= convert(datetime, '12-1-'+convert(varchar, datepart(year,@date) ) )

ELSE IF ( MONTH(@date) >= @fiscalYearStartMonth and MONTH(@date)>1 )

set @inter= convert(datetime, convert(varchar,@fiscalYearStartMonth-1 )+'-1-'+convert(varchar, datepart(year,@date)+1) )

ELSE IF ( MONTH(@date)< @fiscalYearStartMonth )

set @inter= convert(datetime, convert(varchar,@fiscalYearStartMonth-1 )+'-1-'+convert(varchar, datepart(year,@date)) )

return CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@inter))),DATEADD(mm,1,@inter)),101)

end

/**Creation script for configuration table to store timezone offsets from GMT**/

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CFG_DAY_LIGHT_SAVINGS_TIME]') AND type in (N'U'))

DROP TABLE [dbo].[CFG_DAY_LIGHT_SAVINGS_TIME]

CREATE TABLE [dbo].[CFG_DAY_LIGHT_SAVINGS_TIME](

[BEGIN_DATE] [datetime] NULL,

[END_DATE] [datetime] NULL,

[YEAR_DATE] [smallint] NULL

) ON [PRIMARY]

insert into CFG_DAY_LIGHT_SAVINGS_TIME values

('2001-04-01 00:00:00.000', '2001-10-27 00:00:00.000', 2001),

('2002-04-07 00:00:00.000', '2002-10-26 00:00:00.000', 2002),

('2003-04-06 00:00:00.000', '2003-10-25 00:00:00.000', 2003),

('2004-04-04 00:00:00.000', '2004-10-30 00:00:00.000', 2004),

('2005-04-03 00:00:00.000', '2005-10-29 00:00:00.000', 2005),

('2006-04-02 00:00:00.000', '2006-10-28 00:00:00.000', 2006),

('2007-03-11 00:00:00.000', '2007-11-03 00:00:00.000', 2007),

('2008-03-09 00:00:00.000', '2008-11-01 00:00:00.000', 2008),

('2009-03-08 00:00:00.000', '2009-10-31 00:00:00.000', 2009),

('2010-03-14 00:00:00.000', '2010-11-06 00:00:00.000', 2010),

('2011-03-13 00:00:00.000', '2011-11-05 00:00:00.000', 2011),

('2012-03-11 00:00:00.000', '2012-11-03 00:00:00.000', 2012),

('2013-03-10 00:00:00.000', '2013-11-02 00:00:00.000', 2013),

('2014-03-09 00:00:00.000', '2014-11-01 00:00:00.000', 2014),

('2015-03-08 00:00:00.000', '2015-10-31 00:00:00.000', 2015),

('2016-03-13 00:00:00.000', '2016-11-05 00:00:00.000', 2016),

('2017-03-12 00:00:00.000', '2017-11-04 00:00:00.000', 2017),

('2018-03-11 00:00:00.000', '2018-11-03 00:00:00.000', 2018),

('2019-03-10 00:00:00.000', '2019-11-02 00:00:00.000', 2019),

('2020-03-08 00:00:00.000', '2020-10-31 00:00:00.000', 2020),

('2021-03-14 00:00:00.000', '2021-11-06 00:00:00.000', 2021),

('2022-03-13 00:00:00.000', '2022-11-05 00:00:00.000', 2022),

('2023-03-12 00:00:00.000', '2023-11-04 00:00:00.000', 2023),

('2024-03-10 00:00:00.000', '2024-11-02 00:00:00.000', 2024),

('2025-03-09 00:00:00.000', '2025-11-01 00:00:00.000', 2025),

('1967-04-30 00:00:00.000', '1967-10-29 00:00:00.000', 1967),

('1968-04-28 00:00:00.000', '1968-10-27 00:00:00.000', 1968),

('1969-04-27 00:00:00.000', '1969-10-26 00:00:00.000', 1969),

('1970-04-26 00:00:00.000', '1970-10-25 00:00:00.000', 1970),

('1971-04-25 00:00:00.000', '1971-10-31 00:00:00.000', 1971),

('1972-04-30 00:00:00.000', '1972-10-29 00:00:00.000', 1972),

('1973-04-29 00:00:00.000', '1973-10-28 00:00:00.000', 1973),

('1974-01-06 00:00:00.000', '1974-10-27 00:00:00.000', 1974),

('1975-02-23 00:00:00.000', '1975-10-26 00:00:00.000', 1975),

('1976-04-25 00:00:00.000', '1976-10-31 00:00:00.000', 1976),

('1977-04-24 00:00:00.000', '1977-10-31 00:00:00.000', 1977),

('1978-04-30 00:00:00.000', '1978-10-29 00:00:00.000', 1978),

('1979-04-29 00:00:00.000', '1979-10-28 00:00:00.000', 1979),

('1980-04-27 00:00:00.000', '1980-10-26 00:00:00.000', 1980),

('1981-04-26 00:00:00.000', '1981-10-25 00:00:00.000', 1981),

('1982-04-25 00:00:00.000', '1982-10-25 00:00:00.000', 1982),

('1983-04-24 00:00:00.000', '1983-10-30 00:00:00.000', 1983),

('1984-04-29 00:00:00.000', '1984-10-28 00:00:00.000', 1984),

('1985-04-28 00:00:00.000', '1985-10-27 00:00:00.000', 1985),

('1986-04-27 00:00:00.000', '1986-10-26 00:00:00.000', 1986),

('1987-04-05 00:00:00.000', '1987-10-25 00:00:00.000', 1987),

('1988-04-03 00:00:00.000', '1988-10-30 00:00:00.000', 1988),

('1989-04-02 00:00:00.000', '1989-10-29 00:00:00.000', 1989),

('1990-04-01 00:00:00.000', '1990-10-28 00:00:00.000', 1990),

('1991-04-07 00:00:00.000', '1991-10-27 00:00:00.000', 1991),

('1992-04-05 00:00:00.000', '1992-10-25 00:00:00.000', 1992),

('1993-04-04 00:00:00.000', '1993-10-31 00:00:00.000', 1993),

('1994-04-03 00:00:00.000', '1994-10-30 00:00:00.000', 1994),

('1995-04-02 00:00:00.000', '1995-10-29 00:00:00.000', 1995),

('1996-04-07 00:00:00.000', '1996-10-27 00:00:00.000', 1996),

('1997-04-06 00:00:00.000', '1997-10-26 00:00:00.000', 1997),

('1998-04-05 00:00:00.000', '1998-10-25 00:00:00.000', 1998),

('1999-04-04 00:00:00.000', '1999-10-31 00:00:00.000', 1999),

('2000-04-02 00:00:00.000', '2000-10-29 00:00:00.000', 2000)

/**Creation script for DIM_DAY and DIM_MONTH **/

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DIM_DAY]') AND type in (N'U'))

DROP TABLE [dbo].[DIM_DAY]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DIM_MONTH]') AND type in (N'U'))

DROP TABLE [dbo].[DIM_MONTH]

CREATE TABLE [dbo].[DIM_DAY](

[DAY_KEY] [int] NOT NULL,

[FULL_DATE] [datetime] NULL,

[DAY_OF_WEEK] [tinyint] NULL,

[DAY_NUM_IN_MONTH] [tinyint] NULL,

[DAY_NUM_OVERALL] [int] NULL,

[DAY_NAME] [nvarchar](9) NULL,

[DAY_ABBREV] [nchar](3) NULL,

[WEEKDAY_FLAG] [nchar](1) NULL,

[WEEK_NUM_IN_YEAR] [tinyint] NULL,

[WEEK_NUM_OVERALL] [int] NULL,

[WEEK_BEGIN_DATE] [datetime] NULL,

[MONTH] [tinyint] NULL,

[MONTH_NUM_OVERALL] [int] NULL,

[MONTH_NAME] [nvarchar](9) NULL,

[MONTH_ABBREV] [nchar](3) NULL,

[QUARTER] [tinyint] NULL,

[YEAR] [smallint] NULL,

[YEARMO] [int] NULL,

[FISCAL_MONTH] [tinyint] NULL,

[FISCAL_QUARTER] [tinyint] NULL,

[FISCAL_YEAR] [smallint] NULL,

[LAST_DAY_IN_MONTH_FLAG] [nchar](1) NULL,

[SAME_WEEKDAY_YEAR_AGO_DATE] [datetime] NULL,

[FULL_DATE_START_TIME] [datetime] NULL,

[FULL_DATE_END_TIME] [datetime] NULL,

[MINUTES_IN_MONTH] [int] NULL,

[PREVIOUS_MONTH_PREVIOUS_YEAR_FIRST_DAY] [datetime] NULL,

[CURRENT_MONTH_PREVIOUS_YEAR_FIRST_DAY] [datetime] NULL,

[DATE_PREVIOUS_MONTH] [datetime] NULL,

[CURRENT_MONTH_FIRST_DAY] [datetime] NULL,

[DATE_PREVIOUS_MONTH_PREVIOUS_YEAR] [datetime] NULL,

[DAYS_INTO_YEAR] [smallint] NULL,

[DAYS_INTO_QUARTER] [smallint] NULL,

[DAYS_INTO_FISCAL_YEAR] [smallint] NULL,

[DAYS_INTO_FISCAL_QUARTER] [smallint] NULL,

[DAYS_IN_FISCAL_QUARTER] [smallint] NULL,

[DAYS_IN_FISCAL_YEAR] [smallint] NULL,

[DAYS_IN_QUARTER] [smallint] NULL,

[DAYS_IN_YEAR] [smallint] NULL,

[PERCENT_COMPLETION_FISCAL_YEAR] [decimal](4, 3) NULL,

[PERCENT_COMPLETION_FISCAL_QUARTER] [decimal](4, 3) NULL,

[PERCENT_COMPLETION_YEAR] [decimal](4, 3) NULL,

[PERCENT_COMPLETION_QUARTER] [decimal](4, 3) NULL,

[FIRST_DAY_CURRENT_YEAR] [datetime] NULL,

[FIRST_DAY_PREVIOUS_YEAR] [datetime] NULL,

[FIRST_DAY_CURRENT_QUARTER] [datetime] NULL,

[FIRST_DAY_CURRENT_QUARTER_PREVIOUS_YEAR] [datetime] NULL,

[FIRST_DAY_PREVIOUS_QUARTER] [datetime] NULL,

[FIRST_DAY_PREVIOUS_QUARTER_PREVIOUS_YEAR] [datetime] NULL,

[CURRENT_MONTH_FIRST_OF_MONTH] [datetime] NULL,

[CURRENT_MONTH_LAST_OF_MONTH] [datetime] NULL,

[PREVIOUS_1MONTH_FIRST_OF_MONTH] [datetime] NULL,

[PREVIOUS_1MONTH_LAST_OF_MONTH] [datetime] NULL,

[PREVIOUS_2MONTH_FIRST_OF_MONTH] [datetime] NULL,

[PREVIOUS_2MONTH_LAST_OF_MONTH] [datetime] NULL,

[PREVIOUS_3MONTH_FIRST_OF_MONTH] [datetime] NULL,

[PREVIOUS_3MONTH_LAST_OF_MONTH] [datetime] NULL,

[PREVIOUS_4MONTH_FIRST_OF_MONTH] [datetime] NULL,

[PREVIOUS_4MONTH_LAST_OF_MONTH] [datetime] NULL,

[PREVIOUS_5MONTH_FIRST_OF_MONTH] [datetime] NULL,

[PREVIOUS_5MONTH_LAST_OF_MONTH] [datetime] NULL,

[PREVIOUS_6MONTH_FIRST_OF_MONTH] [datetime] NULL,

[PREVIOUS_6MONTH_LAST_OF_MONTH] [datetime] NULL,

[PREVIOUS_7MONTH_FIRST_OF_MONTH] [datetime] NULL,

[PREVIOUS_7MONTH_LAST_OF_MONTH] [datetime] NULL,

[PREVIOUS_8MONTH_FIRST_OF_MONTH] [datetime] NULL,

[PREVIOUS_8MONTH_LAST_OF_MONTH] [datetime] NULL,

[PREVIOUS_9MONTH_FIRST_OF_MONTH] [datetime] NULL,

[PREVIOUS_9MONTH_LAST_OF_MONTH] [datetime] NULL,

[PREVIOUS_10MONTH_FIRST_OF_MONTH] [datetime] NULL,

[PREVIOUS_10MONTH_LAST_OF_MONTH] [datetime] NULL,

[PREVIOUS_11MONTH_FIRST_OF_MONTH] [datetime] NULL,

[PREVIOUS_11MONTH_LAST_OF_MONTH] [datetime] NULL,

[PREVIOUS_12MONTH_FIRST_OF_MONTH] [datetime] NULL,

[PREVIOUS_12MONTH_LAST_OF_MONTH] [datetime] NULL,

[PREVIOUS_13MONTH_FIRST_OF_MONTH] [datetime] NULL,

[PREVIOUS_13MONTH_LAST_OF_MONTH] [datetime] NULL,

[PREVIOUS_14MONTH_FIRST_OF_MONTH] [datetime] NULL,

[PREVIOUS_14MONTH_LAST_OF_MONTH] [datetime] NULL,

[PREVIOUS_15MONTH_FIRST_OF_MONTH] [datetime] NULL,

[PREVIOUS_15MONTH_LAST_OF_MONTH] [datetime] NULL,

[PREVIOUS_16MONTH_FIRST_OF_MONTH] [datetime] NULL,

[PREVIOUS_16MONTH_LAST_OF_MONTH] [datetime] NULL,

[PREVIOUS_17MONTH_FIRST_OF_MONTH] [datetime] NULL,

[PREVIOUS_17MONTH_LAST_OF_MONTH] [datetime] NULL,

[PREVIOUS_18MONTH_FIRST_OF_MONTH] [datetime] NULL,

[PREVIOUS_18MONTH_LAST_OF_MONTH] [datetime] NULL,

[PREVIOUS_19MONTH_FIRST_OF_MONTH] [datetime] NULL,

[PREVIOUS_19MONTH_LAST_OF_MONTH] [datetime] NULL,

[PREVIOUS_20MONTH_FIRST_OF_MONTH] [datetime] NULL,

[PREVIOUS_20MONTH_LAST_OF_MONTH] [datetime] NULL,

[PREVIOUS_21MONTH_FIRST_OF_MONTH] [datetime] NULL,

[PREVIOUS_21MONTH_LAST_OF_MONTH] [datetime] NULL,

[PREVIOUS_22MONTH_FIRST_OF_MONTH] [datetime] NULL,

[PREVIOUS_22MONTH_LAST_OF_MONTH] [datetime] NULL,

[PREVIOUS_23MONTH_FIRST_OF_MONTH] [datetime] NULL,

[PREVIOUS_23MONTH_LAST_OF_MONTH] [datetime] NULL,

[PREVIOUS_24MONTH_FIRST_OF_MONTH] [datetime] NULL,

[PREVIOUS_24MONTH_LAST_OF_MONTH] [datetime] NULL,

[FIRST_DATE_IN_MONTH_FLG] [nchar](1) NULL,

[YYYY_MM_DD] [nchar](8) NULL,

[CURRENT_DATE_FLG] [nchar](1) NULL,

[LAST_MOD_DATE] [datetime] NULL,

[FIRST_QUARTER_FIRST_MONTH_FIRST_DAY] [datetime] NULL,

[FIRST_QUARTER_SECOND_MONTH_FIRST_DAY] [datetime] NULL,

[FIRST_QUARTER_THIRD_MONTH_FIRST_DAY] [datetime] NULL,

[SECOND_QUARTER_FIRST_MONTH_FIRST_DAY] [datetime] NULL,

[SECOND_QUARTER_SECOND_MONTH_FIRST_DAY] [datetime] NULL,

[SECOND_QUARTER_THIRD_MONTH_FIRST_DAY] [datetime] NULL,

[THIRD_QUARTER_FIRST_MONTH_FIRST_DAY] [datetime] NULL,

[THIRD_QUARTER_SECOND_MONTH_FIRST_DAY] [datetime] NULL,

[THIRD_QUARTER_THIRD_MONTH_FIRST_DAY] [datetime] NULL,

[FOURTH_QUARTER_FIRST_MONTH_FIRST_DAY] [datetime] NULL,

[FOURTH_QUARTER_SECOND_MONTH_FIRST_DAY] [datetime] NULL,

[FOURTH_QUARTER_THIRD_MONTH_FIRST_DAY] [datetime] NULL,

[PACIFIC_STANDARD_TIME_OFFSET] [smallint] NULL,

[EASTERN_STANDARD_TIME_OFFSET] [smallint] NULL,

[CENTRAL_STANDARD_TIME_OFFSET] [smallint] NULL,

[MOUNTAIN_STANDARD_TIME_OFFSET] [smallint] NULL,

[HAWAII_TIME_OFFSET] [smallint] NULL,

[ARIZONA_TIME_OFFSET] [smallint] NULL,

[ALASKA_TIME_OFFSET] [smallint] NULL,

CONSTRAINT [pk_dim_day] PRIMARY KEY CLUSTERED

(

[DAY_KEY] ASC

)

-->POPULATION SCRIPT FOR DIM_DAY

IF NOT EXISTS(SELECT 1 FROM DIM_DAY WHERE DAY_KEY = 1)

BEGIN

-->HANDLES 0 RECORD

IF NOT EXISTS(SELECT 1 FROM DIM_DAY WHERE DAY_KEY = 0)

INSERT INTO [dim_day]

([DAY_KEY]

,[LAST_MOD_DATE])

VALUES

(0, GETDATE())

-->DECLARATIONS

DECLARE @YEAR INT, @YEAR_LIMIT INT, @DAY DATETIME, @DAY_KEY INT, @WEEK_OVERALL INT, @CURRENT_MONTH INT, @MONTH INT, @MONTH_COUNTER INT , @FISCAL_MONTH_START INT

/** Configurations UPDATE THESE VALUES FOR DIM_DAY **/

-->SETS THE YEAR TO START FROM

SET @YEAR = 1967

-->SETS THE YEAR TO GO TO

SET @YEAR_LIMIT = 3000

-->SETS THE BEGINNING MONTH OF THE FISCAL YEAR (In this example 7 or July)

SET @FISCAL_MONTH_START=7

/**DO NOT TOUCH BELOW THIS LINE**/

-->INITIALIZES THE DAY_KEY

SET @DAY_KEY =1

-->INITIALIZES THE DAY

SET @DAY =CONVERT(DATETIME, '1/1/'+CONVERT(VARCHAR,@YEAR))

-->INITIALIZES THE WEEK_OVERALL

SET @WEEK_OVERALL =1

-->INITIALIZES THE MONTH

SET @MONTH = 1

-->INITIALIZES THE CURRENT MONTH

SET @CURRENT_MONTH = 1

-->INITIALIZES THE CURRENT MONTH

SET @MONTH_COUNTER = 1

-->LOOP FOR DAYS

WHILE @DAY <=CONVERT(DATETIME, '12/31/'+CONVERT(VARCHAR,@YEAR_LIMIT))

BEGIN

INSERT INTO [dim_day]

([DAY_KEY]

,[FULL_DATE]

,[DAY_OF_WEEK]

,[DAY_NUM_IN_MONTH]

,[DAY_NUM_OVERALL]

,[DAY_NAME]

,[DAY_ABBREV]

,[WEEKDAY_FLAG]

,[WEEK_NUM_IN_YEAR]

,[WEEK_NUM_OVERALL]

,[WEEK_BEGIN_DATE]

,[MONTH]

,[MONTH_NUM_OVERALL]

,[MONTH_NAME]

,[MONTH_ABBREV]

,[QUARTER]

,[YEAR]

,[YEARMO]

,[FISCAL_MONTH]

,[FISCAL_QUARTER]

,[FISCAL_YEAR]

,[LAST_DAY_IN_MONTH_FLAG]

,[SAME_WEEKDAY_YEAR_AGO_DATE]

,[FULL_DATE_START_TIME]

,[FULL_DATE_END_TIME]

,[MINUTES_IN_MONTH]

,[PREVIOUS_MONTH_PREVIOUS_YEAR_FIRST_DAY]

,[CURRENT_MONTH_PREVIOUS_YEAR_FIRST_DAY]

,[DATE_PREVIOUS_MONTH]

,[CURRENT_MONTH_FIRST_DAY]

,[DATE_PREVIOUS_MONTH_PREVIOUS_YEAR]

,[DAYS_INTO_YEAR]

,[DAYS_INTO_QUARTER]

,[DAYS_INTO_FISCAL_YEAR]

,[DAYS_INTO_FISCAL_QUARTER]

,[DAYS_IN_FISCAL_QUARTER]

,[DAYS_IN_FISCAL_YEAR]

,[DAYS_IN_QUARTER]

,[DAYS_IN_YEAR]

,[PERCENT_COMPLETION_FISCAL_YEAR]

,[PERCENT_COMPLETION_FISCAL_QUARTER]

,[PERCENT_COMPLETION_YEAR]

,[PERCENT_COMPLETION_QUARTER]

,[FIRST_DAY_CURRENT_YEAR]

,[FIRST_DAY_PREVIOUS_YEAR]

,[FIRST_DAY_CURRENT_QUARTER]

,[FIRST_DAY_CURRENT_QUARTER_PREVIOUS_YEAR]

,[FIRST_DAY_PREVIOUS_QUARTER]

,[FIRST_DAY_PREVIOUS_QUARTER_PREVIOUS_YEAR]

,[CURRENT_MONTH_FIRST_OF_MONTH]

,[CURRENT_MONTH_LAST_OF_MONTH]

,[PREVIOUS_1MONTH_FIRST_OF_MONTH]

,[PREVIOUS_1MONTH_LAST_OF_MONTH]

,[PREVIOUS_2MONTH_FIRST_OF_MONTH]

,[PREVIOUS_2MONTH_LAST_OF_MONTH]

,[PREVIOUS_3MONTH_FIRST_OF_MONTH]

,[PREVIOUS_3MONTH_LAST_OF_MONTH]

,[PREVIOUS_4MONTH_FIRST_OF_MONTH]

,[PREVIOUS_4MONTH_LAST_OF_MONTH]

,[PREVIOUS_5MONTH_FIRST_OF_MONTH]

,[PREVIOUS_5MONTH_LAST_OF_MONTH]

,[PREVIOUS_6MONTH_FIRST_OF_MONTH]

,[PREVIOUS_6MONTH_LAST_OF_MONTH]

,[PREVIOUS_7MONTH_FIRST_OF_MONTH]

,[PREVIOUS_7MONTH_LAST_OF_MONTH]

,[PREVIOUS_8MONTH_FIRST_OF_MONTH]

,[PREVIOUS_8MONTH_LAST_OF_MONTH]

,[PREVIOUS_9MONTH_FIRST_OF_MONTH]

,[PREVIOUS_9MONTH_LAST_OF_MONTH]

,[PREVIOUS_10MONTH_FIRST_OF_MONTH]

,[PREVIOUS_10MONTH_LAST_OF_MONTH]

,[PREVIOUS_11MONTH_FIRST_OF_MONTH]

,[PREVIOUS_11MONTH_LAST_OF_MONTH]

,[PREVIOUS_12MONTH_FIRST_OF_MONTH]

,[PREVIOUS_12MONTH_LAST_OF_MONTH]

,[PREVIOUS_13MONTH_FIRST_OF_MONTH]

,[PREVIOUS_13MONTH_LAST_OF_MONTH]

,[PREVIOUS_14MONTH_FIRST_OF_MONTH]

,[PREVIOUS_14MONTH_LAST_OF_MONTH]

,[PREVIOUS_15MONTH_FIRST_OF_MONTH]

,[PREVIOUS_15MONTH_LAST_OF_MONTH]

,[PREVIOUS_16MONTH_FIRST_OF_MONTH]

,[PREVIOUS_16MONTH_LAST_OF_MONTH]

,[PREVIOUS_17MONTH_FIRST_OF_MONTH]

,[PREVIOUS_17MONTH_LAST_OF_MONTH]

,[PREVIOUS_18MONTH_FIRST_OF_MONTH]

,[PREVIOUS_18MONTH_LAST_OF_MONTH]

,[PREVIOUS_19MONTH_FIRST_OF_MONTH]

,[PREVIOUS_19MONTH_LAST_OF_MONTH]

,[PREVIOUS_20MONTH_FIRST_OF_MONTH]

,[PREVIOUS_20MONTH_LAST_OF_MONTH]

,[PREVIOUS_21MONTH_FIRST_OF_MONTH]

,[PREVIOUS_21MONTH_LAST_OF_MONTH]

,[PREVIOUS_22MONTH_FIRST_OF_MONTH]

,[PREVIOUS_22MONTH_LAST_OF_MONTH]

,[PREVIOUS_23MONTH_FIRST_OF_MONTH]

,[PREVIOUS_23MONTH_LAST_OF_MONTH]

,[PREVIOUS_24MONTH_FIRST_OF_MONTH]

,[PREVIOUS_24MONTH_LAST_OF_MONTH]

,[FIRST_DATE_IN_MONTH_FLG]

,[YYYY_MM_DD]

,[CURRENT_DATE_FLG]

,[LAST_MOD_DATE])

SELECT

/*[DAY_KEY]*/ @DAY_KEY

/*[FULL_DATE]*/ ,CONVERT(DATE,@DAY)

/*[DAY_OF_WEEK]*/ ,DATEPART(DW, @DAY)

/*[DAY_NUM_IN_MONTH]*/ ,DATEPART(D, @DAY)

/*[DAY_NUM_OVERALL]*/ ,@DAY_KEY

/*[DAY_NAME]*/ ,DATENAME(DW,@DAY)

/*[DAY_ABBREV]*/ ,LEFT(DATENAME(DW,@DAY),3)

/*[WEEKDAY_FLAG]*/ ,CASE WHEN DATEPART(DW, @DAY) = 1 OR DATEPART(DW, @DAY) = 7 THEN 'N' ELSE 'Y' END

/*[WEEK_NUM_IN_YEAR]*/ ,DATEPART(WK, @DAY)

/*[WEEK_NUM_OVERALL]*/ ,@WEEK_OVERALL

/*[WEEK_BEGIN_DATE]*/ ,DBO.fn_GetStartOfWeek(@DAY,1)

/*[MONTH]*/ ,DATEPART(M, @DAY)

/*[MONTH_NUM_OVERALL]*/ ,@MONTH_COUNTER

/*[MONTH_NAME]*/ ,DATENAME(M,@DAY)

/*[MONTH_ABBREV]*/ ,LEFT(DATENAME(M,@DAY) ,3)

/*[QUARTER]*/ ,DATEPART(Q,@DAY)

/*[YEAR]*/ ,DATEPART(Year,@DAY)

/*[YEARMO]*/ ,CONVERT(INT,LEFT(REPLACE(CONVERT(VARCHAR(10), DATEADD(MM,0,@DAY),20),'-',''),6))

/*[FISCAL_MONTH]*/ ,dbo.fn_GetFiscalMonth(@DAY,@FISCAL_MONTH_START)

/*[FISCAL_QUARTER]*/ ,dbo.fn_GetFiscalQuarter(@DAY,@FISCAL_MONTH_START)

/*[FISCAL_YEAR]*/ ,dbo.fn_GetFiscalYear(@DAY,@FISCAL_MONTH_START)

/*[LAST_DAY_IN_MONTH_FLAG]*/ ,CASE WHEN DATEPART(M,@DAY) <> DATEPART(M,DATEADD(D,1,@DAY)) THEN 'Y' ELSE 'N' END

/*[SAME_WEEKDAY_YEAR_AGO_DATE]*/ ,DATEADD(D, -364,@DAY)

/*[FULL_DATE_START_TIME]*/ ,@DAY

/*[FULL_DATE_END_TIME]*/ ,DATEADD(S,-1,DATEADD(D,1,@DAY))

/*[MINUTES_IN_MONTH]*/ ,DATEPART(DAY, DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,CONVERT(DATETIME,CONVERT(VARCHAR,DATEPART(M,@DAY))+'/1/'+CONVERT(VARCHAR,(DATEPART(YEAR,@DAY)))))+1,0))) *1440

/*[PREVIOUS_MONTH_PREVIOUS_YEAR_FIRST_DAY]*/,DATEADD(YEAR,-1,DATEADD(M,-1,CONVERT(DATETIME,CONVERT(VARCHAR,DATEPART(M,@DAY))+'/1/'+CONVERT(CHAR,(DATEPART(YEAR,@DAY))))))

/*[CURRENT_MONTH_PREVIOUS_YEAR_FIRST_DAY]*/ ,DATEADD(YEAR,-1,CONVERT(DATETIME,CONVERT(VARCHAR,DATEPART(M,@DAY))+'/1/'+CONVERT(CHAR,(DATEPART(YEAR,@DAY)))))

/*[DATE_PREVIOUS_MONTH]*/ ,DATEADD(M,-1, @DAY)

/*[CURRENT_MONTH_FIRST_DAY]*/ ,CONVERT(DATETIME,CONVERT(VARCHAR,DATEPART(M,@DAY))+'/1/'+CONVERT(CHAR,(DATEPART(YEAR,@DAY))))

/*[DATE_PREVIOUS_MONTH_PREVIOUS_YEAR]*/ ,DATEADD(M,-1,DATEADD(YEAR, -1,@DAY))

/*[DAYS_INTO_YEAR]*/ ,DATEDIFF(D, CONVERT(DATETIME,'1/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY))), @DAY)+1

/*[DAYS_INTO_QUARTER]*/ ,DATEDIFF(D, CASE DATEPART(Q, @DAY)

WHEN 1 THEN CONVERT(DATETIME,'1/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY)))

WHEN 2 THEN CONVERT(DATETIME,'4/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY)))

WHEN 3 THEN CONVERT(DATETIME,'7/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY)))

WHEN 4 THEN CONVERT(DATETIME,'10/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY)))

END,

@DAY)+1

/*[DAYS_INTO_FISCAL_YEAR]*/ ,DATEDIFF(day,dbo.fn_GetStartOfFiscalYear(@DAY,@FISCAL_MONTH_START) , @DAY)

/*[DAYS_INTO_FISCAL_QUARTER]*/ ,datediff(day,dbo.fn_GetStartOfFiscalQuarter(@DAY,@FISCAL_MONTH_START), @DAY)

/*[DAYS_IN_FISCAL_QUARTER]*/ , DATEDIFF(day,dbo.fn_GetStartOfFiscalQuarter(@DAY,@FISCAL_MONTH_START),dbo.fn_GetEndOfFiscalQuarter(@DAY,@FISCAL_MONTH_START))

/*[DAYS_IN_FISCAL_YEAR]*/ ,DATEDIFF(day,dbo.fn_GetStartOfFiscalYear(@DAY,@FISCAL_MONTH_START),dbo.fn_GetEndOfFiscalYear(@DAY,@FISCAL_MONTH_START))

/*[DAYS_IN_QUARTER]*/ ,CASE DATEPART(Q, @DAY)

WHEN 1 THEN DATEDIFF(D,CONVERT(DATETIME,'1/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY))),CONVERT(DATETIME,'3/31/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY))))

WHEN 2 THEN DATEDIFF(D,CONVERT(DATETIME,'4/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY))),CONVERT(DATETIME,'6/30/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY))))

WHEN 3 THEN DATEDIFF(D,CONVERT(DATETIME,'7/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY))),CONVERT(DATETIME,'9/30/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY))))

WHEN 4 THEN DATEDIFF(D,CONVERT(DATETIME,'10/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY))),CONVERT(DATETIME,'12/31/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY))))

END +1

/*[DAYS_IN_YEAR]*/ ,DATEDIFF(D,CONVERT(DATETIME,'1/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY))),CONVERT(DATETIME,'12/31/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY))))+1

/*[PERCENT_COMPLETION_FISCAL_YEAR]*/ ,convert(decimal(5,2),datediff(day,dbo.fn_GetStartOfFiscalQuarter(@DAY,@FISCAL_MONTH_START),@DAY))/convert(decimal(5,2),DATEDIFF(day,dbo.fn_GetStartOfFiscalYear(@DAY,@FISCAL_MONTH_START),dbo.fn_GetEndOfFiscalYear(@DAY,@FISCAL_MONTH_START)))

/*[PERCENT_COMPLETION_FISCAL_QUARTER]*/ ,convert(decimal(5,2), datediff(day,dbo.fn_GetStartOfFiscalQuarter(@DAY,@FISCAL_MONTH_START), @DAY))/convert(decimal(5,2),DATEDIFF(day,dbo.fn_GetStartOfFiscalQuarter(@DAY,@FISCAL_MONTH_START),dbo.fn_GetEndOfFiscalQuarter(@DAY,@FISCAL_MONTH_START)))

/*[PERCENT_COMPLETION_YEAR]*/ ,convert(decimal(5,2),(DATEDIFF(D, CONVERT(DATETIME,'1/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY))), @DAY)+1)) /convert(decimal(5,2),(DATEDIFF(D,CONVERT(DATETIME,'1/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY))),CONVERT(DATETIME,'12/31/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY))))+1))

/*[PERCENT_COMPLETION_QUARTER]*/ ,convert(decimal(5,2),(DATEDIFF(D, CASE DATEPART(Q, @DAY)

WHEN 1 THEN CONVERT(DATETIME,'1/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY)))

WHEN 2 THEN CONVERT(DATETIME,'4/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY)))

WHEN 3 THEN CONVERT(DATETIME,'7/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY)))

WHEN 4 THEN CONVERT(DATETIME,'10/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY)))

END,

@DAY)+1)) / convert(decimal(5,2),(CASE DATEPART(Q, @DAY)

WHEN 1 THEN DATEDIFF(D,CONVERT(DATETIME,'1/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY))),CONVERT(DATETIME,'3/31/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY))))

WHEN 2 THEN DATEDIFF(D,CONVERT(DATETIME,'4/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY))),CONVERT(DATETIME,'6/30/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY))))

WHEN 3 THEN DATEDIFF(D,CONVERT(DATETIME,'7/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY))),CONVERT(DATETIME,'9/30/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY))))

WHEN 4 THEN DATEDIFF(D,CONVERT(DATETIME,'10/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY))),CONVERT(DATETIME,'12/31/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY))))

END +1))

/*[FIRST_DAY_CURRENT_YEAR]*/ ,CONVERT(DATETIME,'1/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY)))

/*[FIRST_DAY_PREVIOUS_YEAR]*/ ,CONVERT(DATETIME,'1/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY)-1))

/*[FIRST_DAY_CURRENT_QUARTER]*/ ,CASE DATEPART(Q, @DAY)

WHEN 1 THEN CONVERT(DATETIME,'1/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY)))

WHEN 2 THEN CONVERT(DATETIME,'4/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY)))

WHEN 3 THEN CONVERT(DATETIME,'7/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY)))

WHEN 4 THEN CONVERT(DATETIME,'10/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY)))

END

/*[FIRST_DAY_CURRENT_QUARTER_PREVIOUS_YEAR]*/ ,CASE DATEPART(Q, @DAY)

WHEN 1 THEN CONVERT(DATETIME,'1/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY)-1))

WHEN 2 THEN CONVERT(DATETIME,'4/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY)-1))

WHEN 3 THEN CONVERT(DATETIME,'7/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY)-1))

WHEN 4 THEN CONVERT(DATETIME,'10/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY)-1))

END

/*[FIRST_DAY_PREVIOUS_QUARTER]*/ ,CASE DATEPART(Q, @DAY)

WHEN 1 THEN CONVERT(DATETIME,'10/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY)-1))

WHEN 2 THEN CONVERT(DATETIME,'1/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY)))

WHEN 3 THEN CONVERT(DATETIME,'4/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY)))

WHEN 4 THEN CONVERT(DATETIME,'7/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY)))

END

/*[FIRST_DAY_PREVIOUS_QUARTER_PREVIOUS_YEAR]*/,CASE DATEPART(Q, @DAY)

WHEN 1 THEN CONVERT(DATETIME,'10/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY)-2))

WHEN 2 THEN CONVERT(DATETIME,'1/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY)-1))

WHEN 3 THEN CONVERT(DATETIME,'4/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY)-1))

WHEN 4 THEN CONVERT(DATETIME,'7/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY)-1))

END

/*[CURRENT_MONTH_FIRST_OF_MONTH]*/ ,dbo.fn_GetCurrentMonthFirstDay(@DAY)

/*[CURRENT_MONTH_LAST_OF_MONTH]*/ ,DATEADD(S,-1,DATEADD(D,1,dbo.fn_GetCurrentMonthLastDay(@DAY)))

/*[PREVIOUS_1MONTH_FIRST_OF MONTH]*/ ,dbo.fn_GetLastMonthFirstDay(dateadd(m,-0,@DAY))

/*[PREVIOUS_1MONTH_LAST_OF MONTH]*/ ,DATEADD(S,-1,DATEADD(D,1,dbo.fn_GetLastMonthLastDay(dateadd(m,-0,@DAY))))

/*[PREVIOUS_2MONTH_FIRST_OF MONTH]*/ ,dbo.fn_GetLastMonthFirstDay(dateadd(m,-1,@DAY))

/*[PREVIOUS_2MONTH_LAST_OF MONTH]*/ ,DATEADD(S,-1,DATEADD(D,1,dbo.fn_GetLastMonthLastDay(dateadd(m,-1,@DAY))))

/*[PREVIOUS_3MONTH_FIRST_OF MONTH]*/ ,dbo.fn_GetLastMonthFirstDay(dateadd(m,-2,@DAY))

/*[PREVIOUS_3MONTH_LAST_OF MONTH]*/ ,DATEADD(S,-1,DATEADD(D,1,dbo.fn_GetLastMonthLastDay(dateadd(m,-2,@DAY))))

/*[PREVIOUS_4MONTH_FIRST_OF MONTH]*/ ,dbo.fn_GetLastMonthFirstDay(dateadd(m,-3,@DAY))

/*[PREVIOUS_4MONTH_LAST_OF MONTH]*/ ,DATEADD(S,-1,DATEADD(D,1,dbo.fn_GetLastMonthLastDay(dateadd(m,-3,@DAY))))

/*[PREVIOUS_5MONTH_FIRST_OF MONTH]*/ ,dbo.fn_GetLastMonthFirstDay(dateadd(m,-4,@DAY))

/*[PREVIOUS_5MONTH_LAST_OF MONTH]*/ ,DATEADD(S,-1,DATEADD(D,1,dbo.fn_GetLastMonthLastDay(dateadd(m,-4,@DAY))))

/*[PREVIOUS_6MONTH_FIRST_OF MONTH]*/ ,dbo.fn_GetLastMonthFirstDay(dateadd(m,-5,@DAY))

/*[PREVIOUS_6MONTH_LAST_OF MONTH]*/ ,DATEADD(S,-1,DATEADD(D,1,dbo.fn_GetLastMonthLastDay(dateadd(m,-5,@DAY))))

/*[PREVIOUS_7MONTH_FIRST_OF MONTH]*/ ,dbo.fn_GetLastMonthFirstDay(dateadd(m,-6,@DAY))

/*[PREVIOUS_7MONTH_LAST_OF MONTH]*/ ,DATEADD(S,-1,DATEADD(D,1,dbo.fn_GetLastMonthLastDay(dateadd(m,-6,@DAY))))

/*[PREVIOUS_8MONTH_FIRST_OF MONTH]*/ ,dbo.fn_GetLastMonthFirstDay(dateadd(m,-7,@DAY))

/*[PREVIOUS_8MONTH_LAST_OF MONTH]*/ ,DATEADD(S,-1,DATEADD(D,1,dbo.fn_GetLastMonthLastDay(dateadd(m,-7,@DAY))))

/*[PREVIOUS_9MONTH_FIRST_OF MONTH]*/ ,dbo.fn_GetLastMonthFirstDay(dateadd(m,-8,@DAY))

/*[PREVIOUS_9MONTH_LAST_OF MONTH]*/ ,DATEADD(S,-1,DATEADD(D,1,dbo.fn_GetLastMonthLastDay(dateadd(m,-8,@DAY))))

/*[PREVIOUS_10MONTH_FIRST_OF MONTH]*/ ,dbo.fn_GetLastMonthFirstDay(dateadd(m,-9,@DAY))

/*[PREVIOUS_10MONTH_LAST_OF MONTH]*/ ,DATEADD(S,-1,DATEADD(D,1,dbo.fn_GetLastMonthLastDay(dateadd(m,-9,@DAY))))

/*[PREVIOUS_11MONTH_FIRST_OF MONTH]*/ ,dbo.fn_GetLastMonthFirstDay(dateadd(m,-10,@DAY))

/*[PREVIOUS_11MONTH_LAST_OF MONTH]*/ ,DATEADD(S,-1,DATEADD(D,1,dbo.fn_GetLastMonthLastDay(dateadd(m,-10,@DAY))))

/*[PREVIOUS_12MONTH_FIRST_OF MONTH]*/ ,dbo.fn_GetLastMonthFirstDay(dateadd(m,-11,@DAY))

/*[PREVIOUS_12MONTH_LAST_OF MONTH]*/ ,DATEADD(S,-1,DATEADD(D,1,dbo.fn_GetLastMonthLastDay(dateadd(m,-11,@DAY))))

/*[PREVIOUS_13MONTH_FIRST_OF MONTH]*/ ,dbo.fn_GetLastMonthFirstDay(dateadd(m,-12,@DAY))

/*[PREVIOUS_13MONTH_LAST_OF MONTH]*/ ,DATEADD(S,-1,DATEADD(D,1,dbo.fn_GetLastMonthLastDay(dateadd(m,-12,@DAY))))

/*[PREVIOUS_14MONTH_FIRST_OF MONTH]*/ ,dbo.fn_GetLastMonthFirstDay(dateadd(m,-13,@DAY))

/*[PREVIOUS_14MONTH_LAST_OF MONTH]*/ ,DATEADD(S,-1,DATEADD(D,1,dbo.fn_GetLastMonthLastDay(dateadd(m,-13,@DAY))))

/*[PREVIOUS_15MONTH_FIRST_OF MONTH]*/ ,dbo.fn_GetLastMonthFirstDay(dateadd(m,-14,@DAY))

/*[PREVIOUS_15MONTH_LAST_OF MONTH]*/ ,DATEADD(S,-1,DATEADD(D,1,dbo.fn_GetLastMonthLastDay(dateadd(m,-14,@DAY))))

/*[PREVIOUS_16MONTH_FIRST_OF MONTH]*/ ,dbo.fn_GetLastMonthFirstDay(dateadd(m,-15,@DAY))

/*[PREVIOUS_16MONTH_LAST_OF MONTH]*/ ,DATEADD(S,-1,DATEADD(D,1,dbo.fn_GetLastMonthLastDay(dateadd(m,-15,@DAY))))

/*[PREVIOUS_17MONTH_FIRST_OF MONTH]*/ ,dbo.fn_GetLastMonthFirstDay(dateadd(m,-16,@DAY))

/*[PREVIOUS_17MONTH_LAST_OF MONTH]*/ ,DATEADD(S,-1,DATEADD(D,1,dbo.fn_GetLastMonthLastDay(dateadd(m,-16,@DAY))))

/*[PREVIOUS_18MONTH_FIRST_OF MONTH]*/ ,dbo.fn_GetLastMonthFirstDay(dateadd(m,-17,@DAY))

/*[PREVIOUS_18MONTH_LAST_OF MONTH]*/ ,DATEADD(S,-1,DATEADD(D,1,dbo.fn_GetLastMonthLastDay(dateadd(m,-17,@DAY))))

/*[PREVIOUS_19MONTH_FIRST_OF MONTH]*/ ,dbo.fn_GetLastMonthFirstDay(dateadd(m,-18,@DAY))

/*[PREVIOUS_19MONTH_LAST_OF MONTH]*/ ,DATEADD(S,-1,DATEADD(D,1,dbo.fn_GetLastMonthLastDay(dateadd(m,-18,@DAY))))

/*[PREVIOUS_20MONTH_FIRST_OF MONTH]*/ ,dbo.fn_GetLastMonthFirstDay(dateadd(m,-19,@DAY))

/*[PREVIOUS_20MONTH_LAST_OF MONTH]*/ ,DATEADD(S,-1,DATEADD(D,1,dbo.fn_GetLastMonthLastDay(dateadd(m,-19,@DAY))))

/*[PREVIOUS_21MONTH_FIRST_OF MONTH]*/ ,dbo.fn_GetLastMonthFirstDay(dateadd(m,-20,@DAY))

/*[PREVIOUS_21MONTH_LAST_OF MONTH]*/ ,DATEADD(S,-1,DATEADD(D,1,dbo.fn_GetLastMonthLastDay(dateadd(m,-20,@DAY))))

/*[PREVIOUS_22MONTH_FIRST_OF MONTH]*/ ,dbo.fn_GetLastMonthFirstDay(dateadd(m,-21,@DAY))

/*[PREVIOUS_22MONTH_LAST_OF MONTH]*/ ,DATEADD(S,-1,DATEADD(D,1,dbo.fn_GetLastMonthLastDay(dateadd(m,-21,@DAY))))

/*[PREVIOUS_23MONTH_FIRST_OF MONTH]*/ ,dbo.fn_GetLastMonthFirstDay(dateadd(m,-22,@DAY))

/*[PREVIOUS_23MONTH_LAST_OF MONTH]*/ ,DATEADD(S,-1,DATEADD(D,1,dbo.fn_GetLastMonthLastDay(dateadd(m,-22,@DAY))))

/*[PREVIOUS_24MONTH_FIRST_OF MONTH]*/ ,dbo.fn_GetLastMonthFirstDay(dateadd(m,-23,@DAY))

/*[PREVIOUS_24MONTH_LAST_OF MONTH]*/ ,DATEADD(S,-1,DATEADD(D,1,dbo.fn_GetLastMonthLastDay(dateadd(m,-23,@DAY))))

/*[FIRST_DATE_IN_MONTH_FLG]*/ ,CASE WHEN CONVERT(DATETIME,CONVERT(VARCHAR,DATEPART(M,@DAY))+'/1/'+CONVERT(VARCHAR,DATEPART(YEAR,@DAY)))=@DAY THEN 'Y' ELSE 'N' END

/*[YYYY_MM_DD]*/ ,dbo.fn_GetCurrentDateYYYYMMDD(@DAY)

/*[CURRENT_DATE_FLG]*/ ,case when dbo.fn_RemoveTime(getdate())=@DAY then 'Y' else 'N' end

/*[LAST_MOD_DATE]*/ ,GETDATE()

-->INCREMENTS THE WEEK OVERALL NUMBER WHEN THE DATE IN THE LOOP HITS A SATURDAY

IF DATEPART(DW, @DAY) = 7

SET @WEEK_OVERALL = @WEEK_OVERALL+1

-->INCREMENTS THE DAY AND GETS A NEW SURROGATE KEY

SET @DAY = DATEADD(DAY, 1, @DAY)

SET @DAY_KEY = @DAY_KEY+1

-->SETS THE MONTH TO THE NUMERICAL VALUE FOR THE DAY

SET @MONTH = DATEPART(M, @DAY)

-->IF THE MONTH CHANGES THE MONTH OVERALL GETS INCREMENTED

IF @MONTH <> @CURRENT_MONTH

BEGIN

SET @CURRENT_MONTH = @MONTH

SET @MONTH_COUNTER = @MONTH_COUNTER +1

END

UPDATE A

set FIRST_QUARTER_FIRST_MONTH_FIRST_DAY ='1/1/'+ convert(varchar,DATEPART(YEAR,A.FULL_DATE)),

FIRST_QUARTER_SECOND_MONTH_FIRST_DAY ='2/1/'+ convert(varchar,DATEPART(YEAR,A.FULL_DATE)),

FIRST_QUARTER_THIRD_MONTH_FIRST_DAY ='3/1/'+ convert(varchar,DATEPART(YEAR,A.FULL_DATE)),

SECOND_QUARTER_FIRST_MONTH_FIRST_DAY ='4/1/'+ convert(varchar,DATEPART(YEAR,A.FULL_DATE)),

SECOND_QUARTER_SECOND_MONTH_FIRST_DAY ='5/1/'+ convert(varchar,DATEPART(YEAR,A.FULL_DATE)),

SECOND_QUARTER_THIRD_MONTH_FIRST_DAY ='6/1/'+ convert(varchar,DATEPART(YEAR,A.FULL_DATE)),

THIRD_QUARTER_FIRST_MONTH_FIRST_DAY ='7/1/'+ convert(varchar,DATEPART(YEAR,A.FULL_DATE)),

THIRD_QUARTER_SECOND_MONTH_FIRST_DAY ='8/1/'+ convert(varchar,DATEPART(YEAR,A.FULL_DATE)),

THIRD_QUARTER_THIRD_MONTH_FIRST_DAY ='9/1/'+ convert(varchar,DATEPART(YEAR,A.FULL_DATE)),

FOURTH_QUARTER_FIRST_MONTH_FIRST_DAY ='10/1/'+ convert(varchar,DATEPART(YEAR,A.FULL_DATE)),

FOURTH_QUARTER_SECOND_MONTH_FIRST_DAY ='11/1/'+ convert(varchar,DATEPART(YEAR,A.FULL_DATE)),

FOURTH_QUARTER_THIRD_MONTH_FIRST_DAY ='12/1/'+ convert(varchar,DATEPART(YEAR,A.FULL_DATE))

FROM DIM_DAY A

UPDATE d

SET d.CURRENT_DATE_FLG = CASE WHEN dbo.fn_RemoveTime(getdate()) = FULL_DATE_START_TIME THEN 'Y' ELSE 'N' END

FROM DIM_DAY d;

-->Create script for DIM_MONTH

CREATE TABLE [dbo].[DIM_MONTH](

[MONTH_KEY] [smallint] NOT NULL,

[YEARMO] [int] NULL,

[NUM_WORK_DAYS] [int] NULL,

[NUM_HOLIDAYS] [int] NULL,

[NUM_DAYS] [int] NULL,

[NUM_MINUTES] [int] NULL,

[LAST_MOD_DATE] [datetime] NULL,

[MONTH_NAME] [nvarchar](25) NULL,

[MONTH_ABRV_NAME] [nchar](3) NULL,

[YEAR] [smallint] NULL,

[MONTH_NUMBER] [smallint] NULL,

[QUARTER] [smallint] NULL,

[FISCAL_QUARTER] [smallint] NULL,

[FISCAL_YEAR] [smallint] NULL,

[FIRST_DATE_IN_MONTH] [datetime] NULL,

[LAST_DATE_IN_MONTH] [datetime] NULL,

[CURRENT_MONTH_FLG] [nchar](1) NULL,

CONSTRAINT [pk_dim_month] PRIMARY KEY CLUSTERED

(

[MONTH_KEY] ASC

))

-->Population script for DIM_MONTH

-->HANDLES 0 RECORD

IF NOT EXISTS(SELECT 1 FROM DIM_MONTH WHERE MONTH_KEY = 0)

INSERT INTO DIM_MONTH VALUES(0, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)

-->DECLARATIONS

DECLARE @YEAR INT, @YEAR_LIMIT INT, @MONTH INT, @MONTH_KEY INT, @NUM_WORK_DAYS INT, @NUM_HOLIDAYS INT , @NUM_DAYS INT, @NUM_MINUTES INT, @NEWYEARDAY INT, @WEEKEND INT,@YEARMONTH INT, @MONTHTXT CHAR(2)

/** Configurations UPDATE THESE VALUES FOR DIM_MONTH **/

-->SETS THE YEAR TO START FROM

SET @YEAR = 1967

-->SETS THE YEAR TO GO TO

SET @YEAR_LIMIT = 3000

/** DO NOT TOUCH ANYTHING BELOW THIS LINE **/

-->SETS THE MONTH TO START AT

SET @MONTH_KEY = 1

-->OUTER LOOP FOR YEARS

WHILE @YEAR <= @YEAR_LIMIT

BEGIN

-->INNER LOOP FOR MONTHS

SET @MONTH =1

WHILE @MONTH <=12

BEGIN

-->GETS NEW YEARS DAY FOR NEXT YEAR

SELECT @NEWYEARDAY = DATEPART(WEEKDAY,CONVERT(DATETIME, '1/1/'+CONVERT(VARCHAR,@YEAR+1)))

-->GETS NUMBER OF DAYS IN MONTH

SELECT @NUM_DAYS = DATEPART(DAY, DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,(CONVERT(DATETIME,CONVERT(VARCHAR,@MONTH)+'/1/'+CONVERT(VARCHAR,@YEAR))) )+1,0)))

-->GETS NUMBER OF MINUTES IN MONTH

SELECT @NUM_MINUTES = 1440 * @NUM_DAYS

-->CALCULATES MINUTES PER MONTH

SELECT @NUM_HOLIDAYS = CASE WHEN @MONTH IN (5, 7, 9)OR(@MONTH =12 AND @NEWYEARDAY<>7) THEN 1

WHEN @MONTH = 11 OR (@MONTH =12 AND @NEWYEARDAY= 7) THEN 2

WHEN @MONTH = 1 AND (@NEWYEARDAY-1)<>7 THEN 1

ELSE 0 END

-->CALCULATES THE NUMBER OF DAYS M-F THERE ARE IN A MONTH

SELECT @WEEKEND = DBO.FN_GETWORKINGDAYS(CONVERT(DATETIME,CONVERT(VARCHAR,@MONTH)+'/1/'+CONVERT(VARCHAR,@YEAR)),DBO.FN_REMOVETIME(DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,(CONVERT(DATETIME,CONVERT(VARCHAR,@MONTH)+'/1/'+CONVERT(VARCHAR,@YEAR))) )+1,0))))

-->CALCULATES THE NUMBER OF WORK DAYS IN A MONTH

SELECT @NUM_WORK_DAYS = @WEEKEND -@NUM_HOLIDAYS

--CONCATENATES YEAR AND MONTH

SELECT @MONTHTXT = CASE WHEN @MONTH<10 THEN '0'+CONVERT(VARCHAR(2),@MONTH)ELSE CONVERT(VARCHAR(2),@MONTH)END

SELECT @YEARMONTH =CONVERT(INT,CONVERT(VARCHAR(4),@YEAR)+ @MONTHTXT)

INSERT INTO DIM_MONTH

(

MONTH_KEY ,

YEARMO ,

NUM_WORK_DAYS ,

NUM_HOLIDAYS ,

NUM_DAYS,

NUM_MINUTES ,

LAST_MOD_DATE

)

SELECT

@MONTH_KEY,

@YEARMONTH,

@NUM_WORK_DAYS,

@NUM_HOLIDAYS ,

@NUM_DAYS,

@NUM_MINUTES ,

GETDATE()

SET @MONTH = @MONTH+1

SET @MONTH_KEY = @MONTH_KEY+1

END

SET @YEAR = @YEAR+1

END

UPDATE

SET

.MONTH_NAME = CASE

WHEN LEFT(CONVERT(VARCHAR,CONVERT(DATETIME,CONVERT(VARCHAR, m.YEARMO) + '01'),107), 3) = 'Jan' THEN 'January'

WHEN LEFT(CONVERT(VARCHAR,CONVERT(DATETIME,CONVERT(VARCHAR, m.YEARMO) + '01'),107), 3) = 'Feb' THEN 'February'

WHEN LEFT(CONVERT(VARCHAR,CONVERT(DATETIME,CONVERT(VARCHAR, m.YEARMO) + '01'),107), 3) = 'Mar' THEN 'March'

WHEN LEFT(CONVERT(VARCHAR,CONVERT(DATETIME,CONVERT(VARCHAR, m.YEARMO) + '01'),107), 3) = 'Apr' THEN 'April'

WHEN LEFT(CONVERT(VARCHAR,CONVERT(DATETIME,CONVERT(VARCHAR, m.YEARMO) + '01'),107), 3) = 'May' THEN 'May'

WHEN LEFT(CONVERT(VARCHAR,CONVERT(DATETIME,CONVERT(VARCHAR, m.YEARMO) + '01'),107), 3) = 'Jun' THEN 'June'

WHEN LEFT(CONVERT(VARCHAR,CONVERT(DATETIME,CONVERT(VARCHAR, m.YEARMO) + '01'),107), 3) = 'Jul' THEN 'July'

WHEN LEFT(CONVERT(VARCHAR,CONVERT(DATETIME,CONVERT(VARCHAR, m.YEARMO) + '01'),107), 3) = 'Aug' THEN 'August'

WHEN LEFT(CONVERT(VARCHAR,CONVERT(DATETIME,CONVERT(VARCHAR, m.YEARMO) + '01'),107), 3) = 'Sep' THEN 'September'

WHEN LEFT(CONVERT(VARCHAR,CONVERT(DATETIME,CONVERT(VARCHAR, m.YEARMO) + '01'),107), 3) = 'Oct' THEN 'October'

WHEN LEFT(CONVERT(VARCHAR,CONVERT(DATETIME,CONVERT(VARCHAR, m.YEARMO) + '01'),107), 3) = 'Nov' THEN 'November'

WHEN LEFT(CONVERT(VARCHAR,CONVERT(DATETIME,CONVERT(VARCHAR, m.YEARMO) + '01'),107), 3) = 'Dec' THEN 'December'

END

MONTH_ABRV_NAME = LEFT(CONVERT(VARCHAR,CONVERT(DATETIME,CONVERT(VARCHAR, m.YEARMO) + '01'),107), 3)

YEAR = CONVERT(SMALLINT,LEFT(m.YEARMO, 4))

MONTH_NUMBER = CONVERT(SMALLINT,RIGHT(m.YEARMO, 2))

QUARTER = (SELECT d.QUARTER FROM DIM_DAY d WHERE d.FULL_DATE = CONVERT(DATETIME,CONVERT(VARCHAR, m.YEARMO) + '01'))

FISCAL_QUARTER = (SELECT d.FISCAL_QUARTER FROM DIM_DAY d WHERE d.FULL_DATE = CONVERT(DATETIME,CONVERT(VARCHAR, m.YEARMO) + '01'))

FISCAL_YEAR = (SELECT d.FISCAL_YEAR FROM DIM_DAY d WHERE d.FULL_DATE = CONVERT(DATETIME,CONVERT(VARCHAR, m.YEARMO) + '01'))

FIRST_DATE_IN_MONTH = CONVERT(DATETIME,CONVERT(VARCHAR, m.YEARMO) + '01')

LAST_DATE_IN_MONTH = dbo.fn_RemoveTime(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,CONVERT(DATETIME,CONVERT(VARCHAR, m.YEARMO) + '01'))+1,0)))

FROM

DIM_MONTH m

-->Sets current month flag

UPDATE m

SET

m.CURRENT_MONTH_FLG = CASE WHEN YEARMO = (SELECT CONVERT(VARCHAR(6), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()+0), 0), 112)) THEN 'Y' ELSE 'N' END

FROM

DIM_MONTH m;

-->Sets time zone offsets

update a

set

a.PACIFIC_STANDARD_TIME_OFFSET =xx.PACIFIC_STANDARD_TIME_OFFSET,

a.EASTERN_STANDARD_TIME_OFFSET =xx.EASTERN_STANDARD_TIME_OFFSET,

CENTRAL_STANDARD_TIME_OFFSET =xx.CENTRAL_STANDARD_TIME_OFFSET,

MOUNTAIN_STANDARD_TIME_OFFSET =xx.MOUNTAIN_STANDARD_TIME_OFFSET,

HAWAII_TIME_OFFSET =xx.HAWAII_TIME_OFFSET,

ARIZONA_TIME_OFFSET =xx.ARIZONA_TIME_OFFSET,

ALASKA_TIME_OFFSET =xx.ALASKA_TIME_OFFSET

from

DIM_DAY a

inner join (

select

DAY_KEY ,

case when a.FULL_DATE between B.begin_date and b.END_DATE then -7 else -8 end as PACIFIC_STANDARD_TIME_OFFSET,

case when a.FULL_DATE between B.begin_date and b.END_DATE then -4 else -5 end as EASTERN_STANDARD_TIME_OFFSET,

case when a.FULL_DATE between B.begin_date and b.END_DATE then -5 else -6 end as CENTRAL_STANDARD_TIME_OFFSET,

case when a.FULL_DATE between B.begin_date and b.END_DATE then -6 else -7 end as MOUNTAIN_STANDARD_TIME_OFFSET,

-10 as HAWAII_TIME_OFFSET,

-7 as ARIZONA_TIME_OFFSET,

case when a.FULL_DATE between B.begin_date and b.END_DATE then -8 else -9 end as ALASKA_TIME_OFFSET

from

DIM_DAY a

inner join

CFG_DAY_LIGHT_SAVINGS_TIME b on a.YEAR=b.YEAR_DATE

)xx on a.DAY_KEY=xx.DAY_KEY;

Dennis and Jim's Data Engineering Blog

Friday, May 10, 2013

A Date Dimension Starter Kit for your Data Warehouse

3 comments: