I have a script that is working fine with the exception of the fiscal time start/end. For example, year0601 should be the 1st day of the fiscal year and year0530 should be the last day of the fiscal year every year. However, when the script runs, it has an offset. Has anyone scripted this? I'm thinking I can repurpose the calendar login and add +5 to the month logic but wanted to check here first as I've seen a number of 454 and 445 solutions and perhaps I am applying it incorrectly. Thank You for your assistance. Here is the link to the original code: 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)
/*******************************************************************************************************************************************************/