Hello, I have been using a date dimension on this site but have modified it to fit my companies' needs. The code I currently have is below. I am trying to get it to properly post week_of_day_in_year and the fiscal for the 365 day is showing null because it's non leap year with 53 week. I've noticed weeks are starting on Mondays when they should be starting on Sundays since I am in the US. Not too sure what exactly the problem is but if anyone has any solutions feel free to let me know. If you notice any other issues feel free to comment as well. This SQL was originally made by
Mubin M. Shaikh[
^] but it doesn't seem active on his post.
--Select DATEPART(QQ , Getdate()) as DayOfMonthValue
--Select CONVERT (char(8),Getdate(),112)
--DATEPART(DW, @CurrentDate)
--Select CONVERT (char(10),Getdate(),103)
--select DATENAME(DW, '16-aug-2013') AS DayName
--select DATEPART(DW, '16-aug-2013') AS DayNumber
--select DATEPART(WW, '16-aug-2013') AS WeekOfYear
BEGIN TRY
DROP TABLE [EDW_MDM].[dbo].[DimDate]
END TRY
BEGIN CATCH
END CATCH
SET DATEFORMAT mdy;
CREATE TABLE [EDW_MDM].[dbo].[dimdate]
( [date_key] INT primary key,
[date] DATETIME,
[full_date_dmy] CHAR(10), -- Date in dd-MM-yyyy format
[full_date_mdy] CHAR(10),-- Date in MM-dd-yyyy format
[day_of_month] VARCHAR(2), -- Field will hold day number of Month
[day_suffix] VARCHAR(4), -- Apply suffix as 1st, 2nd ,3rd etc
[day_name] VARCHAR(9), -- Contains name of the day, Sunday, Monday
[day_of_week] CHAR(1),-- First Day Sunday=1 and Saturday=7
[day_of_week_monday_start] CHAR(1),-- First Day Monday=1 and Sunday=7
[week_of_day_in_month] VARCHAR(2), --1st Monday or 2nd Monday in Month
[week_of_day_in_year] VARCHAR(2),
[day_of_quarter] VARCHAR(3),
[day_of_year] VARCHAR(3),
[week_of_month] VARCHAR(1),-- Week Number of Month
[week_of_quarter] VARCHAR(2), --Week Number of the Quarter
[week_of_year] VARCHAR(2),--Week Number of the Year
[month] VARCHAR(2), --Number of the Month 1 to 12
[month_name] VARCHAR(9),--January, February etc
[month_of_quarter] VARCHAR(2),-- Month Number belongs to Quarter
[quarter] CHAR(1),
[quarter_name] VARCHAR(9),--First,Second..
[year] CHAR(4),-- Year value of Date stored in Row
[year_name] CHAR(7), --CY 2012,CY 2013
[month_year] CHAR(10), --Jan-2013,Feb-2013
[MMYYYY] CHAR(6),
[first_day_of_month] DATE,
[last_day_of_month] DATE,
[first_day_of_quarter] DATE,
[last_day_of_quarter] DATE,
[first_day_of_year] DATE,
[last_day_of_year] DATE,
[is_holiday] BIT,-- Flag 1=National Holiday, 0-No National Holiday
[is_weekday] BIT,-- 0=Week End ,1=Week Day
[holiday_name] VARCHAR(50),--Name of Holiday in US
[placeholder_value_1] BIT Null, -- Placeholder value will not run if deleted for some reason
[placeholder_value_2] VARCHAR(50) Null --Placeholder value will not run if deleted for some reason
)
GO
--Specify Start Date and End date here
--Value of Start Date Must be Less than Your End Date
DECLARE @StartDate DATETIME = '01/01/2000' --Starting value of Date Range
DECLARE @EndDate DATETIME = '01/01/2050' --End Value of Date Range
--Temporary Variables To Hold the Values During Processing of Each Date of Year
DECLARE
@DayOfWeekInMonth INT,
@DayOfWeekInYear INT,
@DayOfQuarter INT,
@WeekOfMonth INT,
@CurrentYear INT,
@CurrentMonth INT,
@CurrentQuarter INT
DECLARE @DayOfWeek TABLE (DOW INT, MonthCount INT, QuarterCount INT, YearCount INT)
INSERT INTO @DayOfWeek VALUES (1, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (2, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (3, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (4, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (5, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (6, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (7, 0, 0, 0)
--Extract and assign various parts of Values from Current Date to Variable
DECLARE @CurrentDate AS DATETIME = @StartDate
SET @CurrentMonth = DATEPART(MM, @CurrentDate)
SET @CurrentYear = DATEPART(YY, @CurrentDate)
SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)
--Proceed only if Start Date(Current date ) is less than End date you specified above
WHILE @CurrentDate < @EndDate
BEGIN
IF @CurrentMonth != DATEPART(MM, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET MonthCount = 0
SET @CurrentMonth = DATEPART(MM, @CurrentDate)
END
IF @CurrentQuarter != DATEPART(QQ, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET QuarterCount = 0
SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)
END
IF @CurrentYear != DATEPART(YY, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET YearCount = 0
SET @CurrentYear = DATEPART(YY, @CurrentDate)
END
-- Set values in table data type created above from variables
UPDATE @DayOfWeek
SET
MonthCount = MonthCount + 1,
QuarterCount = QuarterCount + 1,
YearCount = YearCount + 1
WHERE DOW = DATEPART(DW, @CurrentDate)
SELECT
@DayOfWeekInMonth = MonthCount,
@DayOfQuarter = QuarterCount,
@DayOfWeekInYear = YearCount
FROM @DayOfWeek
WHERE DOW = DATEPART(DW, @CurrentDate)
INSERT INTO [EDW_MDM].[dbo].[dimdate]
SELECT
CONVERT (char(8),@CurrentDate,112) as date_key,
@CurrentDate AS date,
CONVERT (char(10),@CurrentDate,103) as full_date_dmy,
CONVERT (char(10),@CurrentDate,101) as full_date_mdy,
DATEPART(DD, @CurrentDate) AS day_of_month,
--Apply Suffix values like 1st, 2nd 3rd etc..
CASE
WHEN DATEPART(DD,@CurrentDate) IN (11,12,13) THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'th'
WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 1 THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'st'
WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 2 THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'nd'
WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 3 THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'rd'
ELSE CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'th'
END AS day_suffix,
DATENAME(DW, @CurrentDate) AS day_name,
DATEPART(DW, @CurrentDate) AS day_of_week,
-- check for day of week as Per US and change it as per Monday Start format
CASE DATEPART(DW, @CurrentDate)
WHEN 1 THEN 7
WHEN 2 THEN 1
WHEN 3 THEN 2
WHEN 4 THEN 3
WHEN 5 THEN 4
WHEN 6 THEN 5
WHEN 7 THEN 6
END
AS day_of_week_monday_start,
@DayOfWeekInMonth AS week_of_day_in_month,
@DayOfWeekInYear AS week_of_day_in_year,
@DayOfQuarter AS day_of_quarter,
DATEPART(DY, @CurrentDate) AS day_of_year,
DATEPART(WW, @CurrentDate) + 1 - DATEPART(WW, CONVERT(VARCHAR, DATEPART(MM, @CurrentDate)) + '/1/' + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate))) AS week_of_month,
(DATEDIFF(DD, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), @CurrentDate) / 7) + 1 AS week_of_quarter,
DATEPART(WW, @CurrentDate) AS week_of_year,
DATEPART(MM, @CurrentDate) AS month,
DATENAME(MM, @CurrentDate) AS month_name,
CASE
WHEN DATEPART(MM, @CurrentDate) IN (1, 4, 7, 10) THEN 1
WHEN DATEPART(MM, @CurrentDate) IN (2, 5, 8, 11) THEN 2
WHEN DATEPART(MM, @CurrentDate) IN (3, 6, 9, 12) THEN 3
END AS month_of_quarter,
DATEPART(QQ, @CurrentDate) AS quarter,
CASE DATEPART(QQ, @CurrentDate)
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
END AS Quarter_Name,
DATEPART(YEAR, @CurrentDate) AS year,
'CY ' + CONVERT(VARCHAR, DATEPART(YEAR, @CurrentDate)) AS year_name,
LEFT(DATENAME(MM, @CurrentDate), 3) + '-' + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate)) AS month_year,
RIGHT('0' + CONVERT(VARCHAR, DATEPART(MM, @CurrentDate)),2) + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate)) AS MMYYYY,
CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, @CurrentDate) - 1), @CurrentDate))) AS first_day_of_month,
CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, (DATEADD(MM, 1, @CurrentDate)))), DATEADD(MM, 1, @CurrentDate)))) AS last_day_of_month,
DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0) AS first_day_of_quarter,
DATEADD(QQ, DATEDIFF(QQ, -1, @CurrentDate), -1) AS last_day_of_quarter,
CONVERT(DATETIME, '01/01/' + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate))) AS first_day_of_year,
CONVERT(DATETIME, '12/31/' + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate))) AS last_day_of_year,
NULL AS is_holiday,
CASE DATEPART(DW, @CurrentDate)
WHEN 1 THEN 0
WHEN 2 THEN 1
WHEN 3 THEN 1
WHEN 4 THEN 1
WHEN 5 THEN 1
WHEN 6 THEN 1
WHEN 7 THEN 0
END AS is_weekday,
NULL AS holiday_name, Null, Null
SET @CurrentDate = DATEADD(DD, 1, @CurrentDate)
END
UPDATE [EDW_MDM].[dbo].[dimdate]
SET holiday_name = 'New Year''s Day'
WHERE [Month] = 1 AND [day_of_month] = 1
UPDATE [EDW_MDM].[dbo].[dimdate]
SET holiday_name = 'Martin Luthor King Jr Day'
WHERE
[Month] = 1
AND [day_name] = 'Monday'
AND [year] >= 1983
AND [week_of_month] = 3
UPDATE [EDW_MDM].[dbo].[dimdate]
SET holiday_name = 'President''s Day'
WHERE
[Month] = 2
AND [day_name] = 'Monday'
AND week_of_day_in_month = 3
UPDATE [EDW_MDM].[dbo].[dimdate]
SET holiday_name = 'Memorial Day'
FROM [EDW_MDM].[dbo].[dimdate]
WHERE date_key IN
(
SELECT
MAX(date_key)
FROM [EDW_MDM].[dbo].[dimdate]
WHERE
[month_name] = 'May'
AND [day_name] = 'Monday'
GROUP BY
[Year],
[Month]
)
UPDATE [EDW_MDM].[dbo].[dimdate]
SET holiday_name = 'Independance Day'
WHERE [Month] = 7 AND [day_of_month] = 4
UPDATE [EDW_MDM].[dbo].[dimdate]
SET holiday_name = 'Labor Day'
FROM [EDW_MDM].[dbo].[dimdate]
WHERE date_key IN
(
SELECT
MIN(date_key)
FROM [EDW_MDM].[dbo].[dimdate]
WHERE
[month_name] = 'September'
AND [day_name] = 'Monday'
GROUP BY
[Year],
[Month]
)
UPDATE [EDW_MDM].[dbo].[dimdate]
SET holiday_name = 'Columbus Day'
WHERE [month] = 10 AND [week_of_month] = 2 AND [day_name] = 'Monday'
UPDATE [EDW_MDM].[dbo].[dimdate]
SET holiday_name = 'Veteran''s Day'
WHERE
[month] = 11
AND [day_of_month] = 11
UPDATE [EDW_MDM].[dbo].[dimdate]
SET holiday_name = 'Thanksgiving Day'
WHERE
[Month] = 11
AND [day_name] = 'Thursday' AND [week_of_month] = 4
UPDATE [EDW_MDM].[dbo].[dimdate]
SET holiday_name = 'Christmas Day'
WHERE [Month] = 12 AND [day_of_month] = 25
UPDATE [EDW_MDM].[dbo].[dimdate]
SET is_holiday = CASE WHEN holiday_name IS NULL THEN 0 WHEN holiday_name IS NOT NULL THEN 1 END
--select * from DimDate
--Script 2 fiscal calendar setting in Date dimension
SELECT * FROM [EDW_MDM].[dbo].[dimdate]
ALTER TABLE [dbo].[dimdate] ADD
[fiscal_day_of_year] VARCHAR(3),
[fiscal_week_of_year] VARCHAR(3),
[Fiscal_month] VARCHAR(2),
[fiscal_quarter] CHAR(1),
[fiscal_quarter_name] VARCHAR(9),
[fiscal_year] CHAR(4),
[fiscal_year_name] CHAR(7),
[fiscal_month_year] CHAR(10),
[fiscal_mmyyyy] CHAR(6),
[fiscal_first_day_of_month] DATE,
[fiscal_last_day_of_month] DATE,
[fiscal_first_day_of_quarter] DATE,
[fiscal_last_day_of_quarter] DATE,
[fiscal_first_day_of_year] DATE,
[fiscal_last_day_of_year] DATE
GO
DECLARE
@dtFiscalYearStart SMALLDATETIME = 'January 01, 1995',
@FiscalYear INT = 1995,
@LastYear INT = 2050,
@FirstLeapYearInPeriod INT = 2000
DECLARE
@iTemp INT,
@LeapWeek INT,
@CurrentDate DATETIME,
@FiscalDayOfYear INT,
@FiscalWeekOfYear INT,
@FiscalMonth INT,
@FiscalQuarter INT,
@FiscalQuarterName VARCHAR(10),
@FiscalYearName VARCHAR(7),
@LeapYear INT,
@FiscalFirstDayOfYear DATE,
@FiscalFirstDayOfQuarter DATE,
@FiscalFirstDayOfMonth DATE,
@FiscalLastDayOfYear DATE,
@FiscalLastDayOfQuarter DATE,
@FiscalLastDayOfMonth DATE
DECLARE @LeapTable TABLE (leapyear INT)
DECLARE @tb TABLE(
PeriodDate DATETIME,
[Fiscal_Day_Of_Year] VARCHAR(3),
[Fiscal_Week_Of_Year] VARCHAR(3),
[Fiscal_Month] VARCHAR(2),
[Fiscal_Quarter] VARCHAR(1),
[Fiscal_Quarter_Name] VARCHAR(9),
[Fiscal_Year] VARCHAR(4),
[Fiscal_Year_Name] VARCHAR(7),
[Fiscal_Month_Year] VARCHAR(10),
[Fiscal_MMYYYY] VARCHAR(6),
[Fiscal_First_Day_Of_Month] DATE,
[Fiscal_Last_Day_Of_Month] DATE,
[Fiscal_First_Day_Of_Quarter] DATE,
[Fiscal_Last_Day_Of_Quarter] DATE,
[Fiscal_First_Day_Of_Year] DATE,
[Fiscal_Last_Day_Of_Year] DATE)
SET @LeapYear = @FirstLeapYearInPeriod
WHILE (@LeapYear < @LastYear)
BEGIN
IF ((@LeapYear % 4 = 0 AND @LeapYear % 100 <> 0) OR @LeapYear % 400 = 0)
INSERT INTO @leapTable VALUES (@LeapYear)
SET @LeapYear = @LeapYear + 5
END
SET @CurrentDate = @dtFiscalYearStart
SET @FiscalDayOfYear = 1
SET @FiscalWeekOfYear = 1
SET @FiscalMonth = 1
SET @FiscalQuarter = 1
SET @FiscalWeekOfYear = 1
IF (EXISTS (SELECT * FROM @LeapTable WHERE @FiscalYear = leapyear))
BEGIN
SET @LeapWeek = 1
END
ELSE
BEGIN
SET @LeapWeek = 0
END
WHILE (DATEPART(yy,@CurrentDate) <= @LastYear)
BEGIN
SELECT @FiscalMonth = CASE
WHEN @FiscalWeekOfYear BETWEEN 1 AND 4 THEN 1
WHEN @FiscalWeekOfYear BETWEEN 5 AND 9 THEN 2
WHEN @FiscalWeekOfYear BETWEEN 10 AND 13 THEN 3
WHEN @FiscalWeekOfYear BETWEEN 14 AND 17 THEN 4
WHEN @FiscalWeekOfYear BETWEEN 18 AND 22 THEN 5
WHEN @FiscalWeekOfYear BETWEEN 23 AND 26 THEN 6
WHEN @FiscalWeekOfYear BETWEEN 27 AND 30 THEN 7
WHEN @FiscalWeekOfYear BETWEEN 31 AND 35 THEN 8
WHEN @FiscalWeekOfYear BETWEEN 36 AND 39 THEN 9
WHEN @FiscalWeekOfYear BETWEEN 40 AND 43 THEN 10
WHEN @FiscalWeekOfYear BETWEEN 44 AND 48 THEN 11
WHEN @FiscalWeekOfYear BETWEEN (49) AND (52+@LeapWeek) THEN 12
WHEN @FiscalWeekOfYear BETWEEN 5 AND 8 THEN 2
WHEN @FiscalWeekOfYear BETWEEN 9 AND 13 THEN 3
WHEN @FiscalWeekOfYear BETWEEN 14 AND 17 THEN 4
WHEN @FiscalWeekOfYear BETWEEN 18 AND 21 THEN 5
WHEN @FiscalWeekOfYear BETWEEN 22 AND 26 THEN 6
WHEN @FiscalWeekOfYear BETWEEN 27 AND 30 THEN 7
WHEN @FiscalWeekOfYear BETWEEN 31 AND 34 THEN 8
WHEN @FiscalWeekOfYear BETWEEN 35 AND 39 THEN 9
WHEN @FiscalWeekOfYear BETWEEN 40 AND 43 THEN 10
WHEN @FiscalWeekOfYear BETWEEN 44 AND (47+@leapWeek) THEN 11
WHEN @FiscalWeekOfYear BETWEEN (48+@leapWeek) AND (52+@leapWeek) THEN 12
*/
END
SELECT @FiscalQuarter = CASE
WHEN @FiscalMonth BETWEEN 1 AND 3 THEN 1
WHEN @FiscalMonth BETWEEN 4 AND 6 THEN 2
WHEN @FiscalMonth BETWEEN 7 AND 9 THEN 3
WHEN @FiscalMonth BETWEEN 10 AND 12 THEN 4
END
SELECT @FiscalQuarterName = CASE
WHEN @FiscalMonth BETWEEN 1 AND 3 THEN 'First'
WHEN @FiscalMonth BETWEEN 4 AND 6 THEN 'Second'
WHEN @FiscalMonth BETWEEN 7 AND 9 THEN 'Third'
WHEN @FiscalMonth BETWEEN 10 AND 12 THEN 'Fourth'
END
SELECT @FiscalYearName = 'FY ' + CONVERT(VARCHAR, @FiscalYear)
INSERT INTO @tb (PeriodDate, Fiscal_Day_Of_Year, Fiscal_Week_Of_Year, fiscal_Month, Fiscal_Quarter, Fiscal_Quarter_Name, Fiscal_Year, Fiscal_Year_Name) VALUES
(@CurrentDate, @FiscalDayOfYear, @FiscalWeekOfYear, @FiscalMonth, @FiscalQuarter, @FiscalQuarterName, @FiscalYear, @FiscalYearName)
SET @CurrentDate = DATEADD(dd, 1, @CurrentDate)
SET @FiscalDayOfYear = @FiscalDayOfYear + 1
SET @FiscalWeekOfYear = ((@FiscalDayOfYear-1) / 7) + 1
--IF (@FiscalWeekOfYear > (52+@LeapWeek))
if(@FiscalDayOfYear - @LeapWeek > 365)
BEGIN
SET @FiscalDayOfYear = 1
SET @FiscalWeekOfYear = 1
SET @FiscalYear = @FiscalYear + 1
IF ( EXISTS (SELECT * FROM @leapTable WHERE @FiscalYear = leapyear))
BEGIN
SET @LeapWeek = 1
END
ELSE
BEGIN
SET @LeapWeek = 0
END
END
END
UPDATE @tb
SET
Fiscal_First_Day_Of_Month = minmax.StartDate,
Fiscal_Last_Day_Of_Month = minmax.EndDate
FROM
@tb t,
(
SELECT Fiscal_Month, Fiscal_Quarter, Fiscal_Year, MIN(PeriodDate) AS StartDate, MAX(PeriodDate) AS EndDate
FROM @tb
GROUP BY Fiscal_Month, Fiscal_Quarter, Fiscal_Year
) minmax
WHERE
t.Fiscal_Month = minmax.Fiscal_Month AND
t.Fiscal_Quarter = minmax.Fiscal_Quarter AND
t.Fiscal_Year = minmax.Fiscal_Year
UPDATE @tb
SET
Fiscal_First_Day_Of_Quarter = minmax.StartDate,
Fiscal_Last_Day_Of_Quarter = minmax.EndDate
FROM
@tb t,
(
SELECT Fiscal_Quarter, Fiscal_Year, min(PeriodDate) as StartDate, max(PeriodDate) as EndDate
FROM @tb
GROUP BY Fiscal_Quarter, Fiscal_Year
) minmax
WHERE
t.Fiscal_Quarter = minmax.Fiscal_Quarter AND
t.Fiscal_Year = minmax.Fiscal_Year
UPDATE @tb
SET
Fiscal_First_Day_Of_Year = minmax.StartDate,
Fiscal_Last_Day_Of_Year = minmax.EndDate
FROM
@tb t,
(
SELECT Fiscal_Year, min(PeriodDate) as StartDate, max(PeriodDate) as EndDate
FROM @tb
GROUP BY Fiscal_Year
) minmax
WHERE
t.Fiscal_Year = minmax.Fiscal_Year
UPDATE @tb
SET
Fiscal_Month_Year =
CASE Fiscal_Month
WHEN 1 THEN 'Jan'
WHEN 2 THEN 'Feb'
WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr'
WHEN 5 THEN 'May'
WHEN 6 THEN 'Jun'
WHEN 7 THEN 'Jul'
WHEN 8 THEN 'Aug'
WHEN 9 THEN 'Sep'
WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov'
WHEN 12 THEN 'Dec'
END + '-' + CONVERT(VARCHAR, Fiscal_Year)
UPDATE @tb
SET
Fiscal_MMYYYY = RIGHT('0' + CONVERT(VARCHAR, Fiscal_Month),2) + CONVERT(VARCHAR, Fiscal_Year)
UPDATE [EDW_MDM].[dbo].[dimdate]
SET
Fiscal_Day_Of_Year = a.Fiscal_Day_Of_Year,
Fiscal_Week_Of_Year = a.Fiscal_Week_Of_Year,
Fiscal_Month = a.Fiscal_Month,
Fiscal_Quarter = a.Fiscal_Quarter,
Fiscal_Quarter_Name = a.Fiscal_Quarter_Name,
Fiscal_Year = a.Fiscal_Year,
Fiscal_Year_Name = a.Fiscal_Year_Name,
Fiscal_Month_Year = a.Fiscal_Month_Year,
Fiscal_MMYYYY = a.Fiscal_MMYYYY,
Fiscal_First_Day_Of_Month = a.Fiscal_First_Day_Of_Month,
Fiscal_Last_Day_Of_Month = a.Fiscal_Last_Day_Of_Month,
Fiscal_First_Day_Of_Quarter = a.Fiscal_First_Day_Of_Quarter,
Fiscal_Last_Day_Of_Quarter = a.Fiscal_Last_Day_Of_Quarter,
Fiscal_First_Day_Of_Year = a.Fiscal_First_Day_Of_Year,
Fiscal_Last_Day_Of_Year = a.Fiscal_Last_Day_Of_Year
FROM @tb a
INNER JOIN [EDW_MDM].[dbo].[dimdate] b ON a.PeriodDate = b.[Date]
SELECT
*
FROM [EDW_MDM].[dbo].[dimdate]
What I have tried:
I have tried removing fiscal part but other than that I am still learning SQL so I haven't tried much yet. Just brainstorming now.
Tried @@language and @@datefirst. I believe my issue stems from 52*7 being 364 so there is 53 weeks technically if you start the 365th day being a 53rd week. I need to do a 365 day year with 52 weeks + 1 day.
Tried setting it to initiate on a Sunday. For example I tried 12/31/1995 which is a Sunday and I still have a 365th non leap year day falling on 53rd week. Example is 12/31/1997 being 53 in week_of_day_in_year.
Submit an article or tip