Case WHEN DATEPART(MM,FiscalDate) > FiscalPeriod THEN DATEPART(YYYY, FiscalDate) + 1 ELSE DATEPART(YYYY, FiscalDate) END AS FiscalYear

Case WHEN (DATEPART(MM,FiscalDate) > FiscalPeriod) and (FiscalPeriod = 12) THEN DATEPART(YYYY, FiscalDate) + 1 ELSE DATEPART(YYYY, FiscalDate) END AS FiscalYear

Declare @StartDate as Date = '12/31/2008';WITH YearDays (YearDay, N)AS ( select top 3640 CASE Tally.N%364 WHEN 0 THEN 364 ELSE N%364 END AS YearDay, N FROM Tally ,FWQ (FiscalWeek, FiscalQuarter, YearDay, N)AS ( Select CAST(CEILING(Cast(YearDay as Real)/CAST(7 as Real)) AS INT) as FiscalWeek, CEILING(Cast(YearDay as Real)/CAST(91 as real)) as FicalQuarter, YearDay, N FROM YearDays ,FP ( FiscalDate, FiscalWeekDay, FiscalPeriod, FiscalWeek, FiscalQuarter AS ( SELECT Cast(DATEADD(DD, N, @StartDate) as DATE) AS FiscalDate, CASE DATEPART(DW, DATEADD(dd, N, @StartDate)) 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 FiscalWeekDay, CASE WHEN FiscalWeek%13 BETWEEN 1 AND 5 THEN ((FiscalQuarter-1) * 4) + 1 -(FiscalQuarter-1) WHEN FiscalWeek%13 BETWEEN 6 and 9 THEN ((FiscalQuarter-1) * 4) + 2 -(FiscalQuarter-1) WHEN FiscalWeek%13 > 9 THEN ((FiscalQuarter-1) * 4) + 3 -(FiscalQuarter-1) WHEN FiscalWeek%13 = 0 THEN ((FiscalQuarter-1) * 4) + 3 -(FiscalQuarter-1) END As FiscalPeriod, FiscalWeek, FiscalQuarter FROM Fwq SELECT FP.*, Case WHEN (DATEPART(MM,FiscalDate) > FiscalPeriod) and (FiscalPeriod = 12) THEN DATEPART(YYYY, FiscalDate) + 1 ELSE DATEPART(YYYY, FiscalDate) END AS FiscalYearFROM FP