How to Handle Calculations Related to fiscal year and quarter

  • Comments posted to this topic are about the item How to Handle Calculations Related to fiscal year and quarter

  • Much better to use a date dimension table that contains all relevant date info for your company. You can include columns like 'IsHoliday' or 'IsWeekend' as well as fiscal half-year, quarter, week and day and anything else that is useful to your company. Use a PK of yyyymmdd as an integer. Update the table as needed (maybe only once a year) and join it to your queries instead of calculating these things every time. Avoid errors from the get-go.

    Gerald Britton, Pluralsight courses

  • Agree 🙂

  • g.britton (7/27/2014)


    Much better to use a date dimension table that contains all relevant date info for your company. You can include columns like 'IsHoliday' or 'IsWeekend' as well as fiscal half-year, quarter, week and day and anything else that is useful to your company. Use a PK of yyyymmdd as an integer. Update the table as needed (maybe only once a year) and join it to your queries instead of calculating these things every time. Avoid errors from the get-go.

    Thanks

  • What if your period are 5-4-4?

  • As written, your code would leave out any transactions datestamped for the start time of the period:

    WHERE

    d.DateFirstIssued > fy.FYQ2

    Shouldn't the operator be >= to include the start date and time?

  • g.britton (7/27/2014)


    Much better to use a date dimension table that contains all relevant date info for your company. You can include columns like 'IsHoliday' or 'IsWeekend' as well as fiscal half-year, quarter, week and day and anything else that is useful to your company. Use a PK of yyyymmdd as an integer. Update the table as needed (maybe only once a year) and join it to your queries instead of calculating these things every time. Avoid errors from the get-go.

    Agreed but, particularly if your fiscal year was different than the calendar year, guess what kind of formulas you'd use to make such a table. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • sslyle-1091060 (7/28/2014)


    What if your period are 5-4-4?

    Good question and would make for a good article to exploit the ISO_Week datepart (came out with 2008) of the datetime functions. The question is, what do you want done with years that have 53 fiscal weeks in them? One company I worked at used 6-4-4, 5-4-4, 5-4-4, 5-4-4 and another used 5-4-4, 5-4-4, 5-4-4, 5-4-5.

    Of course, I've also seen some non-US companies that started their work-week on Thursdays instead of Mondays and a lot of US companies that start their work week on Sundays instead of Mondays (working for one right now), which always makes things a bit more interesting in calculating 5-4-4 quarters.

    To be honest, I think calculating things by quarter is a bit archaic now. It was done to make reporting by accountants a whole lot easier in the long gone days before computers.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Maybe I'm just being a bit dense but I would rather use the below script to generate instead of relying on the datediff function and working back from today's date... obviously the below has more columns than you would need for explanation - can anyone point out why mine wouldn't be the preferred route?

    --example table with dates to sort by Fin Year and Fin Quarter

    CREATE TABLE #DatesToTest

    (

    TestDate DATE NOT NULL

    ,FinYear VARCHAR(5) NULL

    , FinYearStart DATE NULL

    ,FinQuarter1Start DATE NULL

    ,FinQuarter2Start DATE NULL

    ,FinQuarter3Start DATE NULL

    ,FinQuarter4Start DATE NULL

    ,FinQuarter INT

    )

    INSERT [#DatesToTest]

    ( [TestDate])

    VALUES ( GETDATE())

    INSERT [#DatesToTest]

    ( [TestDate])

    VALUES ( GETDATE()-365)

    INSERT [#DatesToTest]

    ( [TestDate])

    VALUES ( GETDATE()-180)

    DECLARE @FinYearStart VARCHAR(20)

    SET @FinYearStart = '07-01' -- Use this variable to define the beginning of your financial year

    --PRINT @FinYearStart

    --calculate financial year based off of financial year start date

    SELECT * FROM [#DatesToTest] AS DTT

    UPDATE [#DatesToTest]

    SET [FinYear] = CASE WHEN [TestDate]>=CAST(CAST(YEAR([TestDate]) AS CHAR(4))+'-'+@FinYearStart AS DATE)

    THEN YEAR([TestDate])

    ELSE YEAR([TestDate])-1

    END

    SELECT * FROM [#DatesToTest] AS DTT

    --Set financial year start date into table for easier access and quarter formula

    UPDATE [#DatesToTest]

    SET FinYearStart= CAST([FinYear]+'-'+@FinYearStart AS DATE)

    SELECT * FROM [#DatesToTest] AS DTT

    --set the financial year quarter dates for easy calculations

    UPDATE [#DatesToTest]

    SET [FinQuarter1Start] = [FinYearStart]

    ,[FinQuarter2Start] = DATEADD(MONTH,3,[FinYearStart])

    ,[FinQuarter3Start] = DATEADD(MONTH,6,[FinYearStart])

    ,[FinQuarter4Start] = DATEADD(MONTH,9,[FinYearStart])

    SELECT * FROM [#DatesToTest] AS DTT

    --check which quarter the date falls into

    UPDATE [#DatesToTest]

    SET [FinQuarter] = CASE WHEN [TestDate] >= [FinQuarter4Start] THEN 4

    WHEN [TestDate] >= [FinQuarter3Start] THEN 3

    WHEN [TestDate] >= [FinQuarter2Start] THEN 2

    ELSE 1 END

    SELECT * FROM [#DatesToTest] AS DTT

    --tidy up

    DROP TABLE [#DatesToTest]

  • paul.pennington (7/28/2014)


    As written, your code would leave out any transactions datestamped for the start time of the period:

    WHERE

    d.DateFirstIssued > fy.FYQ2

    Shouldn't the operator be >= to include the start date and time?

    Agree, thanks

  • chris.johnson 1969 (7/28/2014)


    Maybe I'm just being a bit dense but I would rather use the below script to generate instead of relying on the datediff function and working back from today's date... obviously the below has more columns than you would need for explanation - can anyone point out why mine wouldn't be the preferred route?

    --example table with dates to sort by Fin Year and Fin Quarter

    CREATE TABLE #DatesToTest

    (

    TestDate DATE NOT NULL

    ,FinYear VARCHAR(5) NULL

    , FinYearStart DATE NULL

    ,FinQuarter1Start DATE NULL

    ,FinQuarter2Start DATE NULL

    ,FinQuarter3Start DATE NULL

    ,FinQuarter4Start DATE NULL

    ,FinQuarter INT

    )

    INSERT [#DatesToTest]

    ( [TestDate])

    VALUES ( GETDATE())

    INSERT [#DatesToTest]

    ( [TestDate])

    VALUES ( GETDATE()-365)

    INSERT [#DatesToTest]

    ( [TestDate])

    VALUES ( GETDATE()-180)

    DECLARE @FinYearStart VARCHAR(20)

    SET @FinYearStart = '07-01' -- Use this variable to define the beginning of your financial year

    --PRINT @FinYearStart

    --calculate financial year based off of financial year start date

    SELECT * FROM [#DatesToTest] AS DTT

    UPDATE [#DatesToTest]

    SET [FinYear] = CASE WHEN [TestDate]>=CAST(CAST(YEAR([TestDate]) AS CHAR(4))+'-'+@FinYearStart AS DATE)

    THEN YEAR([TestDate])

    ELSE YEAR([TestDate])-1

    END

    SELECT * FROM [#DatesToTest] AS DTT

    --Set financial year start date into table for easier access and quarter formula

    UPDATE [#DatesToTest]

    SET FinYearStart= CAST([FinYear]+'-'+@FinYearStart AS DATE)

    SELECT * FROM [#DatesToTest] AS DTT

    --set the financial year quarter dates for easy calculations

    UPDATE [#DatesToTest]

    SET [FinQuarter1Start] = [FinYearStart]

    ,[FinQuarter2Start] = DATEADD(MONTH,3,[FinYearStart])

    ,[FinQuarter3Start] = DATEADD(MONTH,6,[FinYearStart])

    ,[FinQuarter4Start] = DATEADD(MONTH,9,[FinYearStart])

    SELECT * FROM [#DatesToTest] AS DTT

    --check which quarter the date falls into

    UPDATE [#DatesToTest]

    SET [FinQuarter] = CASE WHEN [TestDate] >= [FinQuarter4Start] THEN 4

    WHEN [TestDate] >= [FinQuarter3Start] THEN 3

    WHEN [TestDate] >= [FinQuarter2Start] THEN 2

    ELSE 1 END

    SELECT * FROM [#DatesToTest] AS DTT

    --tidy up

    DROP TABLE [#DatesToTest]

    There is case in my situation that only pure SELECT is allowed in the query, not even DECLARE, ORDER, ...

  • So how about the below instead? Is there an overhead doing it this way?

    --example table with dates to sort by Fin Year and Fin Quarter

    CREATE TABLE #DatesToTest

    (

    TestDate DATE NOT NULL

    ,FinYear VARCHAR(5) NULL

    , FinYearStart DATE NULL

    ,FinQuarter1Start DATE NULL

    ,FinQuarter2Start DATE NULL

    ,FinQuarter3Start DATE NULL

    ,FinQuarter4Start DATE NULL

    ,FinQuarter INT

    )

    INSERT [#DatesToTest]

    ( [TestDate])

    VALUES ( GETDATE())

    INSERT [#DatesToTest]

    ( [TestDate])

    VALUES ( GETDATE()-365)

    INSERT [#DatesToTest]

    ( [TestDate])

    VALUES ( GETDATE()-180)

    --calculate financial year based off of financial year start date

    SELECT * FROM [#DatesToTest] AS DTT

    UPDATE [#DatesToTest]

    SET [FinYear] = CASE WHEN [TestDate]>=CAST(CAST(YEAR([TestDate]) AS CHAR(4))+'-07-01' AS DATE)

    THEN YEAR([TestDate])

    ELSE YEAR([TestDate])-1

    END

    SELECT * FROM [#DatesToTest] AS DTT

  • Why use formulas at all? I just get the business subject matter expert to give me spreadsheet each year with the values filled in. I bear no responsibility for determining those values. I merely load up what I've been given with a simple SSIS job with some simple sanity checks (e.g. February can't have 30 days and can only have 29 days on Leap years (that's one formula I don't mind coding!)). It takes the SME less than an hour a year to generate the spreadsheet and only a few seconds to load up the new year.

  • I'm not sure what that means, but my philosophy is to let the business tell me what their fiscal periods are and give me the data in a spreadsheet. I just load up what I get.

  • Jeff Moden (7/28/2014)


    g.britton (7/27/2014)


    Much better to use a date dimension table that contains all relevant date info for your company. You can include columns like 'IsHoliday' or 'IsWeekend' as well as fiscal half-year, quarter, week and day and anything else that is useful to your company. Use a PK of yyyymmdd as an integer. Update the table as needed (maybe only once a year) and join it to your queries instead of calculating these things every time. Avoid errors from the get-go.

    Agreed but, particularly if your fiscal year was different than the calendar year, guess what kind of formulas you'd use to make such a table. 😉

    Why use formulas at all? I just get the business subject matter expert to give me spreadsheet each year with the values filled in. I bear no responsibility for determining those values. I merely load up what I've been given with a simple SSIS job with some simple sanity checks (e.g. February can't have 30 days and can only have 29 days on Leap years (that's one formula I don't mind coding!)). It takes the SME less than an hour a year to generate the spreadsheet and only a few seconds to load up the new year.

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

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