Get Fiscal Year Ordered Month List

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: ChillyDBA

Date: 8 Feb 2011

Description

This SP was born from a very specific requirement - the need to provide an SSRS report with a user-picklist of months, but to have the months ordered by the April=>March fiscal year.

I would have epxended some effort to make the fiscal year start configurable, but in 17 years I've only ever encountered 2 variants; January=->December and April=>March. The first one obviously requires no special coding.

Hopefully it will be helpful.

Code

DROP PROCEDURE dbo.usp_GetFiscalYearMonthList

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*

Description: Returns the a list of month number and names

ordered in Fiscal Year order (ie April to March)

Author: ChillyDBA

History: 8 Feb 2011 - Initial Issue

*/

CREATE PROCEDURE dbo.usp_GetFiscalYearMonthList

AS

SELECT

N AS MonthNo,

DATENAME(MONTH,DATEADD(MONTH,N,0)-1) AS MonthName

FROM

(

SELECT TOP (DATEDIFF(

mm,

DATEADD(mm,DATEDIFF(mm,0,'2010-04-01'),0), --First of start month

DATEADD(mm,DATEDIFF(mm,0,'2011-03-01')+1,0))

) --First of month after end month

N = ROW_NUMBER() OVER (ORDER BY t1.OBJECT_ID)

FROM master.sys.All_Columns t1

CROSS JOIN MASTER.sys.All_Columns t2

) t

ORDER BY DATEPART(MONTH,DATEADD(MONTH,-3,DATEADD(MONTH,N,0)-1))

-- exec dbo.usp_GetFiscalYearMonthList