Quite often we need aggregations to be done based on fiscal years rather than on financial years. My preferred method always has been on storing fiscal year values also along with other fields in my calendar table which can be utilized for these type of scenarios.
However, couple of days back there was a question asked by one of my friends on a way to generate the fiscal year values on the fly. He was doing some calculations on a database on which he had no rights to create tables and it didn’t have a calendar table either!
I quickly put together a table valued UDF for him to generate the fiscal year dates on passing a date value which I’m sharing here. The UDF also returns previous and next fiscal year values for year on year comparisons.
The below function returns the current ,previous and next fiscal year start and end dates for a passed date and fiscal start month value. The date passed can be any date of the year and fiscal startmonth denotes the month number corresponding to the start of fiscal year. For example if fiscal year starts at October pass 10,April 4 etc.
IF EXISTS(
SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME=’GetFiscalDates’
AND ROUTINE_TYPE=’FUNCTION’
)
DROP FUNCTION GetFiscalDates
GO
CREATE FUNCTION GetFiscalDates
(
@MonthDate datetime,
@FiscalStartMonth int
)
RETURNS table
AS
RETURN
(
SELECT FiscalStart,
DATEADD(yy,1,FiscalStart)-1 AS FiscalEnd,
DATEADD(yy,-1,FiscalStart) AS PrevYrFiscalStart,
FiscalStart-1 AS PrevYrFiscalEnd,
DATEADD(yy,1,FiscalStart) AS NextYrFiscalStart,
DATEADD(yy,2,FiscalStart)-1 AS NextYrFiscalEnd
FROM
(
SELECT CASE WHEN MONTH(@MonthDate) >= @FiscalStartMonth
THEN DATEADD(mm,DATEDIFF(mm,0,@MonthDate) +(@FiscalStartMonth-MONTH(@MonthDate)),0)
ELSE DATEADD(yy,-1,DATEADD(mm,DATEDIFF(mm,0,@MonthDate) +(@FiscalStartMonth-MONTH(@MonthDate)),0))
END AS FiscalStart
)t
)
Now lets see how function can be invoked
SELECT * FROM dbo.GetFiscalDates(‘20130723’,10)
The result will be as follows
However, couple of days back there was a question asked by one of my friends on a way to generate the fiscal year values on the fly. He was doing some calculations on a database on which he had no rights to create tables and it didn’t have a calendar table either!
I quickly put together a table valued UDF for him to generate the fiscal year dates on passing a date value which I’m sharing here. The UDF also returns previous and next fiscal year values for year on year comparisons.
The below function returns the current ,previous and next fiscal year start and end dates for a passed date and fiscal start month value. The date passed can be any date of the year and fiscal startmonth denotes the month number corresponding to the start of fiscal year. For example if fiscal year starts at October pass 10,April 4 etc.
IF EXISTS(
SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME=’GetFiscalDates’
AND ROUTINE_TYPE=’FUNCTION’
)
DROP FUNCTION GetFiscalDates
GO
CREATE FUNCTION GetFiscalDates
(
@MonthDate datetime,
@FiscalStartMonth int
)
RETURNS table
AS
RETURN
(
SELECT FiscalStart,
DATEADD(yy,1,FiscalStart)-1 AS FiscalEnd,
DATEADD(yy,-1,FiscalStart) AS PrevYrFiscalStart,
FiscalStart-1 AS PrevYrFiscalEnd,
DATEADD(yy,1,FiscalStart) AS NextYrFiscalStart,
DATEADD(yy,2,FiscalStart)-1 AS NextYrFiscalEnd
FROM
(
SELECT CASE WHEN MONTH(@MonthDate) >= @FiscalStartMonth
THEN DATEADD(mm,DATEDIFF(mm,0,@MonthDate) +(@FiscalStartMonth-MONTH(@MonthDate)),0)
ELSE DATEADD(yy,-1,DATEADD(mm,DATEDIFF(mm,0,@MonthDate) +(@FiscalStartMonth-MONTH(@MonthDate)),0))
END AS FiscalStart
)t
)
Now lets see how function can be invoked
SELECT * FROM dbo.GetFiscalDates(‘20130723’,10)
The result will be as follows
As you see the date 20130723 lies in the fiscal year range 20121001 – 20130930 and result gives us the same with corresponding previous and next year date values.
Similarly passing a datevalue of 20131123 gives us the expected result of 20131001 – 20140930
