Sql日期维度非闰年给出第53周 [英] Sql date dimension non leap year giving 53rd week

问题描述

您好，我一直在使用此网站上的日期维度，但已对其进行了修改以满足我公司的需求。我目前的代码如下。我试图让它正确地发布week_of_day_in_year并且365天的财政显示为空，因为它是53周的非闰年。我注意到星期一星期几，因为我在美国，他们应该在周日开始。不太确定究竟是什么问题，但如果有人有任何解决方案，请随时让我知道。如果您发现任何其他问题，请随时发表评论。此SQL最初由 Mubin M. Shaikh [ ^ ]但在他的帖子中似乎没有活动。< br $>

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
	/*No Action*/
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

/*Table Data type to store the day of week count for the month and year*/
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
 
/*Begin day of week logic*/

         /*Check for Change in Month of the Current date if Month changed then 
          Change variable value*/
	IF @CurrentMonth != DATEPART(MM, @CurrentDate) 
	BEGIN
		UPDATE @DayOfWeek
		SET MonthCount = 0
		SET @CurrentMonth = DATEPART(MM, @CurrentDate)
	END

        /* Check for Change in Quarter of the Current date if Quarter changed then change 
         Variable value*/

	IF @CurrentQuarter != DATEPART(QQ, @CurrentDate)
	BEGIN
		UPDATE @DayOfWeek
		SET QuarterCount = 0
		SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)
	END
       
        /* Check for Change in Year of the Current date if Year changed then change 
         Variable value*/
	

	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)
	
/*End day of week logic*/


/* Populate Your Dimension Table with values*/
	
	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


	/*Add HOLIDAYS USA*/	
	
	/*New Years Day*/
	UPDATE [EDW_MDM].[dbo].[dimdate]
		SET holiday_name = 'New Year''s Day'
	WHERE [Month] = 1 AND [day_of_month] = 1

	/*Martin Luthor King Day - Third Monday in January starting in 1983*/
	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

	/*President's Day - Third Monday in February*/
	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

	/*Memorial Day - Last Monday in May*/
	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]
		)

	/*Independence Day*/
	UPDATE [EDW_MDM].[dbo].[dimdate]
		SET holiday_name = 'Independance Day'
	WHERE [Month] = 7 AND [day_of_month] = 4

	/*Labor Day - First Monday in September*/
	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]
		)

	/*Columbus Day - Second Monday in October*/
	UPDATE [EDW_MDM].[dbo].[dimdate]
		SET holiday_name = 'Columbus Day'
	WHERE [month] = 10 AND [week_of_month] = 2 AND [day_name] = 'Monday'

	/*Veterans Day*/
	UPDATE [EDW_MDM].[dbo].[dimdate]
		SET holiday_name = 'Veteran''s Day'
	WHERE
		[month] = 11
		AND [day_of_month] = 11

	/*THANKSGIVING - Fourth THURSDAY in November*/
	UPDATE [EDW_MDM].[dbo].[dimdate]
		SET holiday_name = 'Thanksgiving Day'
	WHERE
		[Month] = 11 
		AND [day_name] = 'Thursday' AND [week_of_month] = 4

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


/*Add Fiscal date columns to 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

/*******************************************************************************************************************************************************
The following section needs to be populated for defining the fiscal calendar
*******************************************************************************************************************************************************/

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

/*Holds the years that have 455 in last quarter*/
DECLARE @LeapTable TABLE (leapyear INT)

/*TABLE to contain the fiscal year calendar*/
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)

/*Populate the table with all leap years*/
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

/*Initiate parameters before loop*/
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

/*******************************************************************************************************************************************************/

/* Loop on days in interval*/
WHILE (DATEPART(yy,@CurrentDate) <= @LastYear)
BEGIN
	
/*SET fiscal Month*/
	SELECT @FiscalMonth = CASE 
		/*Use this section for a 4-5-4 calendar.  Every leap year the result will be a 4-5-5*/
		WHEN @FiscalWeekOfYear BETWEEN 1 AND 4 THEN 1 /*4 weeks*/
		WHEN @FiscalWeekOfYear BETWEEN 5 AND 9 THEN 2 /*5 weeks*/
		WHEN @FiscalWeekOfYear BETWEEN 10 AND 13 THEN 3 /*4 weeks*/
		WHEN @FiscalWeekOfYear BETWEEN 14 AND 17 THEN 4 /*4 weeks*/
		WHEN @FiscalWeekOfYear BETWEEN 18 AND 22 THEN 5 /*5 weeks*/
		WHEN @FiscalWeekOfYear BETWEEN 23 AND 26 THEN 6 /*4 weeks*/
		WHEN @FiscalWeekOfYear BETWEEN 27 AND 30 THEN 7 /*4 weeks*/
		WHEN @FiscalWeekOfYear BETWEEN 31 AND 35 THEN 8 /*5 weeks*/
		WHEN @FiscalWeekOfYear BETWEEN 36 AND 39 THEN 9 /*4 weeks*/
		WHEN @FiscalWeekOfYear BETWEEN 40 AND 43 THEN 10 /*4 weeks*/
		WHEN @FiscalWeekOfYear BETWEEN 44 AND 48 THEN 11 /*5 weeks*/
		WHEN @FiscalWeekOfYear BETWEEN (49) AND (52+@LeapWeek) THEN 12 /*4 weeks (5 weeks on leap correction year)*/
		
		/*Use this section for a 4-4-5 calendar.  Every leap year the result will be a 4-5-5*/
		/*
		WHEN @FiscalWeekOfYear BETWEEN 1 AND 4 THEN 1 /*4 weeks*/
		WHEN @FiscalWeekOfYear BETWEEN 5 AND 8 THEN 2 /*4 weeks*/
		WHEN @FiscalWeekOfYear BETWEEN 9 AND 13 THEN 3 /*5 weeks*/
		WHEN @FiscalWeekOfYear BETWEEN 14 AND 17 THEN 4 /*4 weeks*/
		WHEN @FiscalWeekOfYear BETWEEN 18 AND 21 THEN 5 /*4 weeks*/
		WHEN @FiscalWeekOfYear BETWEEN 22 AND 26 THEN 6 /*5 weeks*/
		WHEN @FiscalWeekOfYear BETWEEN 27 AND 30 THEN 7 /*4 weeks*/
		WHEN @FiscalWeekOfYear BETWEEN 31 AND 34 THEN 8 /*4 weeks*/
		WHEN @FiscalWeekOfYear BETWEEN 35 AND 39 THEN 9 /*5 weeks*/
		WHEN @FiscalWeekOfYear BETWEEN 40 AND 43 THEN 10 /*4 weeks*/
		WHEN @FiscalWeekOfYear BETWEEN 44 AND (47+@leapWeek) THEN 11 /*4 weeks (5 weeks on leap year)*/
		WHEN @FiscalWeekOfYear BETWEEN (48+@leapWeek) AND (52+@leapWeek) THEN 12 /*5 weeks*/
		*/
	END

	/*SET Fiscal Quarter*/
	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
	
	/*Set Fiscal Year Name*/
	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 next day*/
	SET @CurrentDate = DATEADD(dd, 1, @CurrentDate)
	SET @FiscalDayOfYear = @FiscalDayOfYear + 1
	SET @FiscalWeekOfYear = ((@FiscalDayOfYear-1) / 7) + 1


--IF (@FiscalWeekOfYear > (52+@LeapWeek)) /*old*/
if(@FiscalDayOfYear - @LeapWeek > 365)
BEGIN
      /*Reset a new year*/
      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

/*******************************************************************************************************************************************************/

/*Set first and last days of the fiscal months*/
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 

/*Set first and last days of the fiscal quarters*/
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 

/*Set first and last days of the fiscal years*/
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 

/*Set FiscalYearMonth*/
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)

/*Set FiscalMMYYYY*/
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]

我尝试了什么：



我试图删除财政部分，但除了我还在学习SQL所以我还没有尝试过多少。现在就集思广益。



试过@@ language和@@ datefirst。我相信我的问题源于52 * 7为364，所以如果你从第53周开始第365天，技术上有53周。我需要做一个365天的一年，52周+ 1天。

尝试将其设置为在周日开始。例如，我尝试了12/31/1995这是一个星期天，我仍然有第365个非闰年第53周。示例是12/31/1997在week_of_day_in_year中为53。

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.

推荐答案

一周的开始由您的 @@ DATEFIRST [ ^ ]设置。默认值取决于您的 @@ LANGUAGE [ ^ ]设置。



如果 @@ LANGUAGE 返回 us_english ，则 @@ DATEFIRST 的默认值为 7 （星期日）。



如果你在不同的一天看到几周开始，那么要么你使用了错误的语言设置，要么更改了 @@ DATEFIRST 设置。尝试在查询顶部添加 SET DATEFIRST 7; 。

The start of the week is determined by your @@DATEFIRST[^] setting. The default will depend on your @@LANGUAGE[^] setting.

If @@LANGUAGE returns us_english, then the default for @@DATEFIRST will be 7 (Sunday).

If you're seeing weeks start on a different day, then either you're using the wrong language settings, or something has changed the @@DATEFIRST setting. Try adding SET DATEFIRST 7; towards the top of your query.

作为额外的奖励......第1周开始在一年的1月1日，以及随后几周的星期日开始;因此，53周的年份将成为常态，而不是例外。



例如今年（2019年）我们从星期二开始的第1周开始，从1月6日星期日开始，第2周。全年工作从12月29日开始53周。



关于财政年度，最好的办法是找出他们是否使用4- 4-5日历或类似日历（在美国是普通的和合法的）并对其方法进行研究

Just as an added bonus.... Week #1 begins on January 1st of the year, and subsequent weeks begin on Sundays; so 53 week years are going to be the norm and not the exception.

For instance this year (2019) we start off with week-1 starting on a Tuesday, and week-2 beginning on Sunday January 6th. Working through the year finds week-53 beginning on December 29th.

In regards to Fiscal Year, best thing to to is to find out if they are using a 4-4-5 calendar or similar (common and legal in the US) and do research on methods for that

这篇关于Sql日期维度非闰年给出第53周的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！