Display data by fiscal year instead of calendar year

  • Sanjay-300840

    SSC Veteran

    Points: 241

    Comments posted to this topic are about the item Display data by fiscal year instead of calendar year

  • Keld Laursen (SEGES)

    SSC Eights!

    Points: 841

    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

    SSCrazy Eights

    Points: 9893

    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

    SSC Veteran

    Points: 254

    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)

    SSC Eights!

    Points: 841

    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

    SSCrazy

    Points: 2764

    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

    SSC-Insane

    Points: 23096

    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[/url]
    For tips on how to post your problems[/url]

  • Shaira

    SSCrazy

    Points: 2432

    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

    Points: 40

    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.

  • Sanjay-300840

    SSC Veteran

    Points: 241

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

    ,MONTH(DATEADD(m,3,RegistrationDate)) as Fmonth

    This seems to be cool technic. Thanks for posting.

  • stuff 56271

    SSC Eights!

    Points: 878

    I dobt that this is the best way to do this but it is one way - you would not generate the period table dynamically as each period should be 445 forever so you could generate 10 or 20 years in a real table with correct indexes.

    -- Declare a table variable (note - table variables have no indexes and are heaps)

    -- This holds the different periods (4,4,5) calculated from a known start date

    declare @PeriodTable table

    (

    Period varchar(50),

    StartDate datetime,

    EndDate datetime

    )

    -- Our data table - only has a date field but this is your normal data table

    declare @Dates table

    (

    Date datetime

    )

    -- Some variables to help create the data

    declare @WhichPeriod smallint = 3;

    declare @NextDate date

    declare @Date date = '2011-1-1'

    -- First add some dates to the date table

    while (@Date < '2012-1-1')

    begin

    insert into @Dates(Date)

    values (@Date)

    set @Date = Dateadd(day, 1, @Date)

    end

    -- Now add the periods to the period table with known starting date for the first period

    set @Date = '2011-1-1'

    while (@Date < '2012-1-1')

    begin

    set @WhichPeriod=@WhichPeriod + 1;

    if (@WhichPeriod >= 3)

    set @WhichPeriod = 0

    set @NextDate = case when @WhichPeriod = 2 then dateadd(day, 5 * 7, @Date) else dateadd(day, 4*7, @date) end

    insert into @PeriodTable(Period, StartDate, EndDate)

    values(

    case

    when @WhichPeriod = 0 then 'Period 1 (4 Weeks)'

    when @WhichPeriod = 1 then 'Period 2 (4 Weeks)'

    else 'Period 3 (5 Weeks)'

    end,

    @Date,

    dateAdd(day,-1, @NextDate)

    )

    set @Date = @NextDate

    end

    -- Query showing the results of the date and the period

    select * from @Dates d inner join @PeriodTable p

    on d.Date between p.StartDate and p.EndDate

    The results are like this

    Date Period StartDate EndDate

    2011-01-01 00:00:00.000 Period 1 (4 Weeks) 2011-01-01 00:00:00.000 2011-01-28 00:00:00.000

    2011-01-02 00:00:00.000 Period 1 (4 Weeks) 2011-01-01 00:00:00.000 2011-01-28 00:00:00.000

    2011-01-03 00:00:00.000 Period 1 (4 Weeks) 2011-01-01 00:00:00.000 2011-01-28 00:00:00.000

    ...

    2011-01-27 00:00:00.000 Period 1 (4 Weeks) 2011-01-01 00:00:00.000 2011-01-28 00:00:00.000

    2011-01-28 00:00:00.000 Period 1 (4 Weeks) 2011-01-01 00:00:00.000 2011-01-28 00:00:00.000

    2011-01-29 00:00:00.000 Period 2 (4 Weeks) 2011-01-29 00:00:00.000 2011-02-25 00:00:00.000

    2011-01-30 00:00:00.000 Period 2 (4 Weeks) 2011-01-29 00:00:00.000 2011-02-25 00:00:00.000

    ...

    2011-02-24 00:00:00.000 Period 2 (4 Weeks) 2011-01-29 00:00:00.000 2011-02-25 00:00:00.000

    2011-02-25 00:00:00.000 Period 2 (4 Weeks) 2011-01-29 00:00:00.000 2011-02-25 00:00:00.000

    2011-02-26 00:00:00.000 Period 3 (5 Weeks) 2011-02-26 00:00:00.000 2011-04-01 00:00:00.000

    2011-02-27 00:00:00.000 Period 3 (5 Weeks) 2011-02-26 00:00:00.000 2011-04-01 00:00:00.000

    ...

    2011-12-30 00:00:00.000 Period 3 (5 Weeks) 2011-11-26 00:00:00.000 2011-12-30 00:00:00.000

    2011-12-31 00:00:00.000 Period 1 (4 Weeks) 2011-12-31 00:00:00.000 2012-01-27 00:00:00.000

    Chris

  • charles.byrne

    SSC Veteran

    Points: 254

    mtassin (8/7/2012)


    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.

    It really depends on what you mean by complicated.

    If your Calendar Table just has a Date value and no time value then you better be certain that the field you're joining on doesn't have time values. If it does then your join by Date may not return values so you'd have to do a conversion or add another field on the table with just the date. You also have to account for the fact that the date may be outside the range

    of your calendar table.

    The best option is to just have the Fiscal Fields on the table. For most of our stuff we calculate the values during the insert or when the transaction

    is approved/final and it remains static.

    For those who would prefer to use a Calendar Table that only has date values but your source has a date-time values (string and/or date conversion(s) then inner/left join):

    --Just the Date No Time Value

    DATEADD(d,DATEDIFF(d,0,RegistrationDate),0)

    --OR

    CONVERT(DATETIME,(CONVERT(VARCHAR, @MyDate,110)))

    --OR

    CONVERT(VARCHAR,RegistrationDate,110) --MM-DD-YYYY --

    --OR

    CONVERT(VARCHAR,RegistrationDate,112) --YYYYMMDD

    In this scenario, DATEADD(m,RegistrationDate,3) or the table function

    I mentioned earlier seems alot simpler. I know, I know its a Scalar Function, but this logic can be done outside of SQL like a SSRS Calculated field or a static field on the data table if performance is an issue.

    "There is nothing so useless as doing efficiently that which should not be done at all." - Peter Drucker

  • Robin Sasson

    Hall of Fame

    Points: 3359

    Just to echo previous comments I've used Calendar tables in which is held all the necessary fiscal/lunar/holiday information required for the business.

    Even 100 years worth of data held by the day is not that large a table.

    :exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

  • charles.byrne

    SSC Veteran

    Points: 254

    Robin Sasson (8/9/2012)


    Just to echo previous comments I've used Calendar tables in which is held all the necessary fiscal/lunar/holiday information required for the business.

    Even 100 years worth of data held by the day is not that large a table.

    We do have calendar tables for legacy systems, but never more than a few years in the future because of changes in business rules, holidays, business days, etc. A business partner calendar date may change days of business from Mon-Friday to Tues-Saturday. A company may move to a different locale where different holidays are observed etc.

    "There is nothing so useless as doing efficiently that which should not be done at all." - Peter Drucker

  • charles.byrne

    SSC Veteran

    Points: 254

    This is just a caveat to readers of this thread...

    As I stated earlier, Don't build a calendar table on Dates unless you really need to. Again, it depends on the data and deciding on maintenance and complexity of the calendars you need.

    The Fiscal Year in this article is a simple (well defined) time offset by 3 months that can be done in SQL or SSRS. It is not a lunar calendar, a 4-4-5, a holiday calendar, etc. I would just calculate the value (in SQL or outside of SQL) and put it on the original table or when it went into the data warehouse. I wouldn't build/maintain a Calendar table for fiscal years for 100 years. At my place of work we do have Calendar Tables for legacy systems and for constantly changing business rules or for historical and/or effective dating, currency rate history etc.

    If you need to calculate the distance between two points via a Trig function then calculate on the fly as you use them (in or out of sql). If performance is an issue then by all means put it in a table.

    Would you build an Absolute value table so you didn't have to use the ABS function to reduce processing time? If so then what would be your max number in that table?

    Would you build a DateTable for 100 years just to know the day of the week or format the date to different varchar formats? You can I guess and it would be suitable in a table with a 100K or millions of rows, but not

    a few thousand.

    Again, I'm not trying to detract from calendar tables. They may be needed especially in performance critical scenarios and DW environments and where there may be different rules for different situations and substantially large sets of data.

    These kind of things are (sometimes) good candidates for Calendar Tables:

    * Data from multiple countries that observe different holidays

    * A business partner that has different workdays

    (Do they work Tuesday to Saturday compared to your business).

    * A business partner who does 4-4-5 and starts the 2nd Sat in January.

    * Does the next business day start at Midnight or does it start at 3PM?

    * What was the agreed hourly rate from X company on Y date for Z

    services?

    This algorithm in the article, Fiscal Year, was well defined and doesn't require such. If performance is really an issue then do the calculation outside of SQL or by all means build a Calendar table if you must, in memory or permanently and do your join.

    "There is nothing so useless as doing efficiently that which should not be done at all." - Peter Drucker

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply