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... usp_GetFiscalYearMonthList.sql (1k) Andy Hughes, Jun 22, 2012, 7:23 AM
|
