Date: 8 Feb 2011
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.
DROP PROCEDURE dbo.usp_GetFiscalYearMonthList
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
Description: Returns the a list of month number and names
ordered in Fiscal Year order (ie April to March)
History: 8 Feb 2011 - Initial Issue
CREATE PROCEDURE dbo.usp_GetFiscalYearMonthList
N AS MonthNo,
DATENAME(MONTH,DATEADD(MONTH,N,0)-1) AS MonthName
SELECT TOP (DATEDIFF(
DATEADD(mm,DATEDIFF(mm,0,'2010-04-01'),0), --First of start month
) --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
ORDER BY DATEPART(MONTH,DATEADD(MONTH,-3,DATEADD(MONTH,N,0)-1))
-- exec dbo.usp_GetFiscalYearMonthList