Friday, May 10, 2013

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]
go
CREATE FUNCTION [dbo].[fn_RemoveTime]
(@DATE DATETIME)
RETURNS DATETIME
AS
BEGIN
DECLARE @STRIPDATE DATETIME
SET @STRIPDATE =DATEADD(dd, DATEDIFF(dd,0,@DATE), 0)
RETURN @STRIPDATE
END

GO

-->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]
go
CREATE FUNCTION [dbo].[fn_GetWorkingDays]
(@startDate  DATETIME, @endDate  DATETIME)
RETURNS INT
AS
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) 
                IN
                (
                    'Saturday',
                    'Sunday'
                )
            )
    );
END

GO

-->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]
go
CREATE FUNCTION [dbo].[fn_GetStartOfWeek]
(@DATE DATETIME, @WEEK_START_DAY INT=1)
RETURNS DATETIME
AS
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
      END
RETURN @START_OF_WEEK_DATE
END

GO


-->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]
go
CREATE FUNCTION [dbo].[fn_GetLastMonthFirstDay]
(@Date DATETIME)
RETURNS DATETIME
AS
BEGIN
    DECLARE @range DATETIME;

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


GO

-->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]
go
CREATE FUNCTION [dbo].[fn_GetLastMonthLastDay]
(@Date DATETIME)
RETURNS DATETIME
AS
BEGIN
    DECLARE @range DATETIME;

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

GO

-->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]
go
CREATE FUNCTION [dbo].[fn_GetCurrentMonthFirstDay]
(@Date DATETIME)
RETURNS DATETIME
AS
BEGIN
    DECLARE @range DATETIME;

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

GO


-->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]
go
CREATE FUNCTION [dbo].[fn_GetCurrentMonthLastDay]
(@Date DATETIME)
RETURNS DATETIME
AS
BEGIN
    DECLARE @range DATETIME;

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

GO


-->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]
go
Create FUNCTION [dbo].[fn_GetCurrentDateYYYYMMDD]
(@Date DATETIME)
RETURNS NCHAR(8)
AS
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)))<then'0'+convert(nvarchar(2),DATEpart(DD,@Date))
else convert(nvarchar(2),DATEpart(DD,@Date)) end
   RETURN @range
END

GO

-->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]
go
CREATE FUNCTION dbo.fn_GetFiscalYear(
      @date             DATETIME,
      @fiscalYearStartMonth int
)
RETURNS INT
AS
BEGIN
Declare @Answer int
      IF ( MONTH(@date) < @fiscalYearStartMonth)
            SET @Answer = YEAR(@date) - 1
      ELSE
            SET @Answer = YEAR(@date)

            return @Answer
           
            END
            go
           
                   
-->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]
go
CREATE FUNCTION dbo.fn_GetFiscalMonth(
      @date             DATETIME,
      @fiscalYearStartMonth int
)
RETURNS INT
AS
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
            go
           
                         
-->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]
go
CREATE FUNCTION dbo.fn_GetFiscalQuarter(
      @date             DATETIME,
      @fiscalYearStartMonth int
)
RETURNS INT
AS
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
            go
           
           
-->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]
go
CREATE FUNCTION dbo.fn_GetStartOfFiscalQuarter(
      @date             DATETIME,
      @fiscalYearStartMonth int
)
RETURNS DATETIME
AS
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  (,4,7,10) then @beginOfMonth
      when @fmonth  in (,5,8,11) then dateadd(mm,-1,@beginOfMonth)
      when @fmonth  in (,6,9,12) then dateadd(mm,-2,@beginOfMonth)
       end
       return @answer
       end

            go
           
-->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]
go
CREATE FUNCTION dbo.fn_GetEndOfFiscalQuarter(
      @date             DATETIME,
      @fiscalYearStartMonth int
)
RETURNS DATETIME
AS
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  (,4,7,10) then dateadd(mm,+2,@beginOfMonth)
      when @fmonth  in (,5,8,11)  then dateadd(mm,+1,@beginOfMonth)
      when @fmonth  in (,6,9,12) then @beginOfMonth
       end
       return   CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@answer))),DATEADD(mm,1,@answer)),101)
       end

            go
           
-->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]
go
CREATE FUNCTION dbo.fn_GetStartOfFiscalYear(
      @date             DATETIME,
      @fiscalYearStartMonth int
)
RETURNS DATETIME
AS
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)-) )
       
       
       return @answer
       end

            go

 -->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]
go
CREATE FUNCTION dbo.fn_GetEndOfFiscalYear(
      @date             DATETIME,
      @fiscalYearStartMonth int
)
RETURNS DATETIME
AS
BEGIN

declare @inter datetime

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

      ELSE IF ( MONTH(@date) >= @fiscalYearStartMonth and MONTH(@date)>)
      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
            go

/**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]
go 
CREATE TABLE [dbo].[CFG_DAY_LIGHT_SAVINGS_TIME](
      [BEGIN_DATE] [datetime] NULL,
      [END_DATE] [datetime] NULL,
      [YEAR_DATE] [smallint] NULL
) ON [PRIMARY]

GO

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)
go





/**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]
go
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]
go

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
)
)
GO
-->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


END
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
go

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;
go

-->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
))
GO


-->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)<>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

m

SET


m

.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;
go

-->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 -else -end as PACIFIC_STANDARD_TIME_OFFSET,
case when a.FULL_DATE between B.begin_date and b.END_DATE then -else -end as EASTERN_STANDARD_TIME_OFFSET,
case when a.FULL_DATE between B.begin_date and b.END_DATE then -else -end as CENTRAL_STANDARD_TIME_OFFSET,
case when a.FULL_DATE between B.begin_date and b.END_DATE then -else -end as MOUNTAIN_STANDARD_TIME_OFFSET,
-10 as HAWAII_TIME_OFFSET,
-as ARIZONA_TIME_OFFSET,
case when a.FULL_DATE between B.begin_date and b.END_DATE then -else -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;
go



No comments:

Post a Comment