Trogers96 Ответов: 2

Даты в SQL размер не високосный год, давая 53-й неделе

Здравствуйте, я использую измерение даты на этом сайте, но изменил его, чтобы соответствовать потребностям моих компаний. Код, который у меня сейчас есть, приведен ниже. Я пытаюсь заставить его правильно разместить week_of_day_in_year, и фискальный отчет за 365 дней показывает ноль, потому что это не високосный год с 53 неделями. Я заметил, что недели начинаются по понедельникам, когда они должны начинаться по воскресеньям, так как я нахожусь в США. Не слишком уверен, в чем именно заключается проблема, но если у кого-то есть какие-то решения, не стесняйтесь, дайте мне знать. Если вы заметите какие-либо другие проблемы, не стесняйтесь комментировать их. Эта версия изначально была сделана Мубин М. Шейх[^] но он не кажется активным на своем посту.







--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, который является воскресеньем, и у меня все еще есть 365th день не високосного года, приходящийся на 53-ю неделю. Например, 12/31/1997 - 53 в week_of_day_in_year.

2 Ответов

Рейтинг:
10

MadMyche

Просто в качестве дополнительного бонуса.... Неделя № 1 начинается 1 января года, а последующие недели начинаются по воскресеньям; таким образом, 53-недельные годы будут нормой, а не исключением.

Например, в этом году (2019) мы начинаем с Недели-1, начинающейся во вторник, и недели-2, начинающейся в воскресенье 6 января. Работа в течение года находит неделю-53, начинающуюся 29 декабря.

Что касается финансового года, то лучше всего выяснить, используют ли они календарь 4-4-5 или аналогичный (распространенный и легальный в США), и провести исследование методов для этого

Рейтинг:
1

Richard Deeming

Начало недели определяется вашим @@DATEFIRST[^] установка. Значение по умолчанию будет зависеть от вашего @@ЯЗЫК[^] установка.

Если @@LANGUAGE возвращается us_english, то по умолчанию для @@DATEFIRST будет 7 (Воскресенье).

Если вы видите, что недели начинаются в другой день, то либо вы используете неправильные языковые настройки, либо что-то изменилось. @@DATEFIRST установка. Попробуйте добавить SET DATEFIRST 7; к началу вашего запроса.