How to Handle Calculations Related to fiscal year and quarter

  • halifaxdal

    SSCoach

    Points: 19584

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

  • g.britton

    SSChampion

    Points: 13686

    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, MCSE-DP, MVPToronto PASS Chapter[/url]

  • Carrie.Sim

    SSC Rookie

    Points: 46

    Agree 🙂

  • halifaxdal

    SSCoach

    Points: 19584

    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

  • sslyle-1091060

    SSC Enthusiast

    Points: 159

    What if your period are 5-4-4?

  • paul.pennington

    SSC Enthusiast

    Points: 108

    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?

  • Jeff Moden

    SSC Guru

    Points: 994701

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 994701

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • chris.johnson 1969

    SSC Veteran

    Points: 265

    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]

  • halifaxdal

    SSCoach

    Points: 19584

    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

  • halifaxdal

    SSCoach

    Points: 19584

    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, ...

  • chris.johnson 1969

    SSC Veteran

    Points: 265

    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

  • gbritton1

    SSCertifiable

    Points: 6520

    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.

  • gbritton1

    SSCertifiable

    Points: 6520

    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.

  • gbritton1

    SSCertifiable

    Points: 6520

    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