/** 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)))<2 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 (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
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 (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
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)-1 ) )
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=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
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)<>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
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 -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;
go
