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
|
 Updating...
Andy Hughes, Jun 22, 2012, 7:23 AM
|