If you want some flexibility to change fiscal year date ranges the case statements are a bit tedious. I happened to need some fiscal year calculations just the other day so came up with this table-valued function:

CREATE FUNCTION [dbo].[tvfGetFiscalYear]

(

@YearStart VARCHAR(10)

,@YearEnd VARCHAR(10)

,@InputMonth INT

,@InputYear INT

)

RETURNS @FYear TABLE

(

FMonth INT

,FYear INT

,IMonth INT

,IYear INT

)

AS

BEGIN

DECLARE

@FiscalYear INT

,@FiscalMonth INT

,@FiscalYearStart DATE

,@FiscalYearEnd DATE

,@FStartMonth DATE

,@InputDate DATE

SET @InputDate = CAST(CAST(@InputMonth AS VARCHAR(2))+'/01/'+CAST(@InputYear AS VARCHAR(4)) AS DATE)

SET @FiscalYearEnd = CAST(CAST(DATEPART(yyyy,@InputDate) AS CHAR(4))+'/'+@YearEnd AS DATE)

SET @FiscalYearStart = CAST(CAST(DATEPART(yyyy,DATEADD(yy,-1,@InputDate)) AS CHAR(4))+'/'+@YearStart AS DATE)

IF DATEDIFF(day,@FiscalYearEnd,@InputDate) > 0

SET @FiscalYear = YEAR(DATEADD(yy,1,@InputDate))

ELSE IF DATEDIFF(day,@InputDate,@FiscalYearStart) > 0

SET @FiscalYear = YEAR(DATEADD(yy,-1,@InputDate))

ELSE

SET @FiscalYear = YEAR(@InputDate)

SET @FStartMonth = CAST(@YearStart+'/'+CAST(@FiscalYear-1 AS CHAR(4)) AS DATE)

SET @FiscalMonth = CAST(DATEDIFF(MONTH,@FStartMonth,@InputDate) AS INT)+1

INSERT INTO @FYear

SELECT

@FiscalMonth

,@FiscalYear

,@InputMonth

,@InputYear

RETURN

END

Then, use the function like this and get the same results:

DECLARE

@YearStart VARCHAR(10)

,@YearEnd VARCHAR(10)

SET @YearStart = '10/1'

SET @YearEnd = '9/30'

SELECT

COUNT(ur.UserId) AS [User Count]

,DATENAME(MONTH,ur.Registrationdate) AS mnth

,YEAR(ur.Registrationdate) AS Yr

,'FY '+CAST((SELECT FYear FROM dbo.tvfGetFiscalYear(@YearStart,@YearEnd,MONTH(ur.Registrationdate),YEAR(ur.Registrationdate))) AS VARCHAR(7)) AS FY

,(SELECT FMonth FROM dbo.tvfGetFiscalYear(@YearStart,@YearEnd,MONTH(ur.Registrationdate),YEAR(ur.Registrationdate))) AS FYMonth

FROM

dbo.UserRegistration AS ur

WHERE

ur.UserId > 0

GROUP BY

DATENAME(MONTH,ur.Registrationdate)

,YEAR(ur.Registrationdate)

,MONTH(ur.Registrationdate)

ORDER BY

FYMonth

,yr

Probably an even better method would be to use the function's date logic in a stored procedure to calculate the fiscal year/month for all of the rows and then join the results to the UserRegistration table instead of using a function in the select statement. For a large dataset that would likely be more efficient. But I'll leave that for someone else. 😉