## Display data by fiscal year instead of calendar year

 Author Message Sanjay-300840 SSC-Enthusiastic Group: General Forum Members Points: 143 Visits: 124 Comments posted to this topic are about the item Display data by fiscal year instead of calendar year Keld Laursen (SEGES) SSChasing Mays Group: General Forum Members Points: 635 Visits: 217 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 SSCertifiable Group: General Forum Members Points: 6569 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 )ASBEGIN 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 FYMonthFROM dbo.UserRegistration AS urWHERE ur.UserId > 0GROUP 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 SSC-Enthusiastic Group: General Forum Members Points: 150 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 FunctionCREATE FUNCTION dbo.FiscalYear (@SomeDate DateTime)RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT Fyear = YEAR(DATEADD(m,3,@SomeDate)) ,Fmonth = MONTH(DATEADD(m,3,@SomeDate)); GOSELECT fy.Fyear ,fy.Fmonthfrom YourTableCROSS 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) SSChasing Mays Group: General Forum Members Points: 635 Visits: 217 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`I've been burned on calendar tables with fiscal data I find it easier and usually faster to do the DATEADD functionGood advice, and better readability on the result than what I posted. Mike Dougherty-384281 SSCommitted Group: General Forum Members Points: 1738 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 SSCoach Group: General Forum Members Points: 15502 Visits: 72528 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 DBAProud member of the Anti-RBAR alliance.For help with Performance click this linkFor tips on how to post your problems Shaira SSCommitted Group: General Forum Members Points: 1516 Visits: 278 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 SSC Rookie Group: General Forum Members Points: 26 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.