date - SQL DimDate 会计年度不 = 454 或 445 但日历范围

Question

我有一个运行良好的脚本，但财政时间开始/结束除外。例如，year0601 应该是财政年度的第一天，year0530 应该是每年财政年度的最后一天。但是，当脚本运行时，它有一个偏移量。有人写过这个吗？我想我可以重新调整日历登录的用途并将 +5 添加到月份逻辑但想先在这里检查一下，因为我已经看到了许多 454 和 445 解决方案，也许我应用不正确。谢谢您的帮助。这是原始代码的链接： https ://www.codeproject.com/Articles/647950/%2FArticles%2F647950%2FCreate-and-Populate-Date-Dimension-for-Data-Wareho

`

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

/* 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+@LeapWeek) THEN 11 /*5 weeks*/
            --WHEN @FiscalWeekOfYear BETWEEN (49+@LeapWeek) AND (52+@LeapWeek) THEN 12 /*4 weeks (5 weeks on leap 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, FiscalDayOfYear, FiscalWeekOfYear, fiscalMonth, FiscalQuarter, FiscalQuarterName, FiscalYear, FiscalYearName) 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))
       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
       FiscalFirstDayOfMonth = minmax.StartDate,
       FiscalLastDayOfMonth = minmax.EndDate
FROM
@tb t,
       (
       SELECT FiscalMonth, FiscalQuarter, FiscalYear, MIN(PeriodDate) AS StartDate, MAX(PeriodDate) AS EndDate
       FROM @tb
       GROUP BY FiscalMonth, FiscalQuarter, FiscalYear
       ) minmax
WHERE
       t.FiscalMonth = minmax.FiscalMonth AND
       t.FiscalQuarter = minmax.FiscalQuarter AND
       t.FiscalYear = minmax.FiscalYear 

/*Set first and last days of the fiscal quarters*/
UPDATE @tb
SET
       FiscalFirstDayOfQuarter = minmax.StartDate,
       FiscalLastDayOfQuarter = minmax.EndDate
FROM
@tb t,
       (
       SELECT FiscalQuarter, FiscalYear, min(PeriodDate) as StartDate, max(PeriodDate) as EndDate
       FROM @tb
       GROUP BY FiscalQuarter, FiscalYear
       ) minmax
WHERE
       t.FiscalQuarter = minmax.FiscalQuarter AND
       t.FiscalYear = minmax.FiscalYear 

/*Set first and last days of the fiscal years*/
UPDATE @tb
SET
       FiscalFirstDayOfYear = minmax.StartDate,
       FiscalLastDayOfYear = minmax.EndDate
FROM
@tb t,
       (
       SELECT FiscalYear, min(PeriodDate) as StartDate, max(PeriodDate) as EndDate
       FROM @tb
       GROUP BY FiscalYear
       ) minmax
WHERE
       t.FiscalYear = minmax.FiscalYear 

/*Set FiscalYearMonth*/
UPDATE @tb
SET
       FiscalMonthYear = 
              CASE FiscalMonth
              WHEN 1 THEN 'Jun'
              WHEN 2 THEN 'Jul'
              WHEN 3 THEN 'Aug'
              WHEN 4 THEN 'Sep'
              WHEN 5 THEN 'Oct'
              WHEN 6 THEN 'Nov'
              WHEN 7 THEN 'Dec'
              WHEN 8 THEN 'Jan'
              WHEN 9 THEN 'Feb'
              WHEN 10 THEN 'Mar'
              WHEN 11 THEN 'Apr'
              WHEN 12 THEN 'May'
              END + '-' + CONVERT(VARCHAR, FiscalYear)

/*Set FiscalMMYYYY*/
UPDATE @tb
SET
       FiscalMMYYYY = RIGHT('0' + CONVERT(VARCHAR, FiscalMonth),2) + CONVERT(VARCHAR, FiscalYear)

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

标签： datecalendarfiscal