SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Display data by fiscal year instead of calendar year


Display data by fiscal year instead of calendar year

Author
Message
Sanjay-300840
Sanjay-300840
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 124
Comments posted to this topic are about the item Display data by fiscal year instead of calendar year
Keld Laursen (SEGES)
Keld Laursen (SEGES)
Mr or Mrs. 500
Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)

Group: General Forum Members
Points: 528 Visits: 209
Nice article.

As I have to work with harvest years, going from August to July the next year, it is of great interest to me as well.

I am just wondering: Is it much better to do a lot of if statements (a case statement is often a multiple-if statement unless it is very big, in which case it might be converted into a lookup table - not sure what SQL server does) than doing some quick math?

You could have calculated the fiscal month as (MONTH(somedate) + 3) % 12 + 1, which, I think, is less cluttered and much more readable than the other variant.

You could likewise calculate the year as YEAR(somedate) + (MONTH(somedate) + 3) / 12 with an equal increase in readability. Whether or not people understands what happens is another matter, but it should be quite straightforward.

/Keld Laursen
Steven Willis
Steven Willis
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3921 Visits: 1721
If you want some flexibility to change fiscal year date ranges the case statements are a bit tedious. I happened to need some fiscal year calculations just the other day so came up with this table-valued function:


CREATE FUNCTION [dbo].[tvfGetFiscalYear]
(
@YearStart VARCHAR(10)
,@YearEnd VARCHAR(10)
,@InputMonth INT
,@InputYear INT
)
RETURNS @FYear TABLE
(
FMonth INT
,FYear INT
,IMonth INT
,IYear INT
)
AS
BEGIN

DECLARE
@FiscalYear INT
,@FiscalMonth INT
,@FiscalYearStart DATE
,@FiscalYearEnd DATE
,@FStartMonth DATE
,@InputDate DATE

SET @InputDate = CAST(CAST(@InputMonth AS VARCHAR(2))+'/01/'+CAST(@InputYear AS VARCHAR(4)) AS DATE)

SET @FiscalYearEnd = CAST(CAST(DATEPART(yyyy,@InputDate) AS CHAR(4))+'/'+@YearEnd AS DATE)
SET @FiscalYearStart = CAST(CAST(DATEPART(yyyy,DATEADD(yy,-1,@InputDate)) AS CHAR(4))+'/'+@YearStart AS DATE)

IF DATEDIFF(day,@FiscalYearEnd,@InputDate) > 0
SET @FiscalYear = YEAR(DATEADD(yy,1,@InputDate))
ELSE IF DATEDIFF(day,@InputDate,@FiscalYearStart) > 0
SET @FiscalYear = YEAR(DATEADD(yy,-1,@InputDate))
ELSE
SET @FiscalYear = YEAR(@InputDate)

SET @FStartMonth = CAST(@YearStart+'/'+CAST(@FiscalYear-1 AS CHAR(4)) AS DATE)
SET @FiscalMonth = CAST(DATEDIFF(MONTH,@FStartMonth,@InputDate) AS INT)+1


INSERT INTO @FYear
SELECT
@FiscalMonth
,@FiscalYear
,@InputMonth
,@InputYear


RETURN

END



Then, use the function like this and get the same results:


DECLARE
@YearStart VARCHAR(10)
,@YearEnd VARCHAR(10)

SET @YearStart = '10/1'
SET @YearEnd = '9/30'

SELECT
COUNT(ur.UserId) AS [User Count]
,DATENAME(MONTH,ur.Registrationdate) AS mnth
,YEAR(ur.Registrationdate) AS Yr
,'FY '+CAST((SELECT FYear FROM dbo.tvfGetFiscalYear(@YearStart,@YearEnd,MONTH(ur.Registrationdate),YEAR(ur.Registrationdate))) AS VARCHAR(7)) AS FY
,(SELECT FMonth FROM dbo.tvfGetFiscalYear(@YearStart,@YearEnd,MONTH(ur.Registrationdate),YEAR(ur.Registrationdate))) AS FYMonth
FROM
dbo.UserRegistration AS ur
WHERE
ur.UserId > 0
GROUP BY
DATENAME(MONTH,ur.Registrationdate)
,YEAR(ur.Registrationdate)
,MONTH(ur.Registrationdate)
ORDER BY
FYMonth
,yr



Probably an even better method would be to use the function's date logic in a stored procedure to calculate the fiscal year/month for all of the rows and then join the results to the UserRegistration table instead of using a function in the select statement. For a large dataset that would likely be more efficient. But I'll leave that for someone else. ;-)

 
charles.byrne
charles.byrne
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 172
That seems a little complicated for a Fiscal Year function.
The Fiscal Year Logic can be pared down to


select YEAR(DATEADD(m,3,RegistrationDate)) as Fyear
,MONTH(DATEADD(m,3,RegistrationDate)) as Fmonth

-- OR A Table Function

CREATE FUNCTION dbo.FiscalYear
(@SomeDate DateTime)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN SELECT Fyear = YEAR(DATEADD(m,3,@SomeDate))
,Fmonth = MONTH(DATEADD(m,3,@SomeDate))
;
GO

SELECT
fy.Fyear
,fy.Fmonth
from YourTable
CROSS APPLY dbo.FiscalYear(RegistrationDate) fy



Or if you prefer to do it in SSRS create a calculated field with vb.net
=Year(DateAdd(DateInterval.Month,3,Fields!RegistrationDate))
=Month(DateAdd(DateInterval.Month,3,Fields!RegistrationDate))




As stated earlier, you can also build a calendar table in memory and do a join, provided that the Registration Date is just the date value and doesn't have time values. I've been burned on calendar tables with fiscal data I find it easier and usually faster to do the DATEADD function

"There is nothing so useless as doing efficiently that which should not be done at all." - Peter Drucker
Keld Laursen (SEGES)
Keld Laursen (SEGES)
Mr or Mrs. 500
Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)

Group: General Forum Members
Points: 528 Visits: 209
charles.byrne (8/7/2012)
That seems a little complicated for a Fiscal Year function.
The Fiscal Year Logic can be pared down to


select YEAR(DATEADD(m,3,RegistrationDate)) as Fyear
,MONTH(DATEADD(m,3,RegistrationDate)) as Fmonth
<snip>


<snip>
I've been burned on calendar tables with fiscal data I find it easier and usually faster to do the DATEADD function


Good advice, and better readability on the result than what I posted.
Mike Dougherty-384281
Mike Dougherty-384281
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1192 Visits: 944
CELKO (8/7/2012)
Why not use a Calendar table with whatever fiscal or reporting years you use? The GAAP had 250+ the last time I looked. SQL is not a computional language and function calls only prevent optimization.


That was the solution I was expecting to read about. In the classic tradeoff between computation time and storage space, a few thousand rows worth of calendar table to save the overhead of all these function calls and so-called "more readable" maths would be obvious.

If anyone does write an article solving this problem with a calendar table, please comment in this thread with a link so those of us already discussing it do not miss the alternate implementation. Thanks!
mtassin
mtassin
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10616 Visits: 72521
CELKO (8/7/2012)
Why not use a Calendar table with whatever fiscal or reporting years you use? The GAAP had 250+ the last time I looked. SQL is not a computional language and function calls only prevent optimization.


I have to agree with Mr Celko.

My first thought to the solution to this was a Calendar Table with a column for Fiscal Year and a column for Calendar year. Then you don't have to do anything complicated.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Shaira
Shaira
SSC Eights!
SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)

Group: General Forum Members
Points: 836 Visits: 276
I used SSAS to create an extensive calendar table, which includes fiscal year. Then just link by date and you can get any combination of calendar data.
dmccann-847082
dmccann-847082
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 18
This also doesn't address lunar fiscal calendars, which are based on a pattern of weeks for each month, such as 4-4-5, not just a shift in the month number. A calendar table is a much more useful solution.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search