Date DImension Query

  • Hi guys,

    I'm working on modifying the fiscal date script from here.

    I'm trying to figure out how to calculate the following.

    FiscalWkofQrt

    -- Week # of each querter where it resets every quarter (i.e. every quarter would have like 13-15 or so weeks)

    FiscalWkofMn

    -- Week # of each month where it resets every month (i.e. every month would have like 4-5 weeks)

    FiscalDofQrt

    -- Day # of each quarter where it resets every quarter (i.e. every quarter would have like 90+ days or so)

    FiscalDofMn

    -- Day # of each month where it resets every month (i.e. every month would have like 28-35 days)

    FiscalDofWk

    -- Day # of each week where it resets every week (i.e. every week would have 7 days)

    It's kind of hard for me to follow the existing logic and because it's Fiscal calcs, I can't go with the usual built in fx to calculate them based on the date. Any help would be appreciated. Thanks!

  • o103452 (5/5/2016)


    Hi guys,

    I'm working on modifying the fiscal date script from here.

    I'm trying to figure out how to calculate the following.

    FiscalWkofQrt

    -- Week # of each querter where it resets every quarter (i.e. every quarter would have like 13-15 or so weeks)

    FiscalWkofMn

    -- Week # of each month where it resets every month (i.e. every month would have like 4-5 weeks)

    FiscalDofQrt

    -- Day # of each quarter where it resets every quarter (i.e. every quarter would have like 90+ days or so)

    FiscalDofMn

    -- Day # of each month where it resets every month (i.e. every month would have like 28-35 days)

    FiscalDofWk

    -- Day # of each week where it resets every week (i.e. every week would have 7 days)

    It's kind of hard for me to follow the existing logic and because it's Fiscal calcs, I can't go with the usual built in fx to calculate them based on the date. Any help would be appreciated. Thanks!

    Unless your fiscal period starts on different days than the corresponding calendar period, the calculations are identical. You don't mention when your fiscal year starts, but July 1 is very common.

    So the current fiscal and calendar quarter would both start an April 1, and the number of weeks since April 1 would be the same. It doesn't matter that this is the 4th quarter of the fiscal year and the 2nd quarter of the calendar year, because you're only concerned with the number of weeks since the start of the quarter.

    The current fiscal and calendar month would both start on May 1, and the number of weeks (or days) since May 1 would be the same. It doesn't matter that this is the 11th month of the fiscal year and the 5th month of the calendar year, because you're only concerned with the number of weeks since the start of the month.

    That being said, if your fiscal year starts on Oct. 5, for example, you do have much more complicated formulas. So when does your fiscal year start?

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Create a calendar table having a record for each day you company has been or will be filing tax reports.

    Not sure if it's good or bad, but there are not so many days we're living through.

    10k rows table will cover >25 years period.

    Create a column for each Week# and Day# you need to calculate.

    Populate it with numbers using whatever method you may think of.

    Cursors, correlated subqueries - it's OK. Performance does not matter as it's one-off exercise.

    Present the table to your finance people for approval.

    If they find some errors in the numbers - correct them manually.

    Index the table and use it in queries like this:

    SELECT [DayNoPerWeek]

    From dbo.FiscalCalendar

    where [Date] = ....

    _____________
    Code for TallyGenerator

  • drew.allen (5/5/2016)

    Unless your fiscal period starts on different days than the corresponding calendar period, the calculations are identical. You don't mention when your fiscal year starts, but July 1 is very common.

    So the current fiscal and calendar quarter would both start an April 1, and the number of weeks since April 1 would be the same. It doesn't matter that this is the 4th quarter of the fiscal year and the 2nd quarter of the calendar year, because you're only concerned with the number of weeks since the start of the quarter.

    The current fiscal and calendar month would both start on May 1, and the number of weeks (or days) since May 1 would be the same. It doesn't matter that this is the 11th month of the fiscal year and the 5th month of the calendar year, because you're only concerned with the number of weeks since the start of the month.

    That being said, if your fiscal year starts on Oct. 5, for example, you do have much more complicated formulas. So when does your fiscal year start?

    Drew

    sry, should have posted some dates. Also, sry but I'm having trouble understanding your explanation. Let's say the Fiscal year starts on Oct 1 OR... Oct 2. I also need to keep the 4-4-5 and 4-5-4 logic in mind and leap years.

  • Sergiy (5/5/2016)


    Create a calendar table having a record for each day you company has been or will be filing tax reports.

    Not sure if it's good or bad, but there are not so many days we're living through.

    10k rows table will cover >25 years period.

    Create a column for each Week# and Day# you need to calculate.

    Populate it with numbers using whatever method you may think of.

    Cursors, correlated subqueries - it's OK. Performance does not matter as it's one-off exercise.

    Present the table to your finance people for approval.

    If they find some errors in the numbers - correct them manually.

    Index the table and use it in queries like this:

    SELECT [DayNoPerWeek]

    From dbo.FiscalCalendar

    where [Date] = ....

    Thanks for the tip. I'm wondering if there's there a way to improve the current query based on the fiscal year start date and the 4-4-5 or 4-5-4 style/leap year and update values in the current DimDate table? I could probably do this in Excel and import it but am trying to do everything in one script with minimum objects, if possible.

  • o103452 (5/6/2016)


    Sergiy (5/5/2016)


    Create a calendar table having a record for each day you company has been or will be filing tax reports.

    Not sure if it's good or bad, but there are not so many days we're living through.

    10k rows table will cover >25 years period.

    Create a column for each Week# and Day# you need to calculate.

    Populate it with numbers using whatever method you may think of.

    Cursors, correlated subqueries - it's OK. Performance does not matter as it's one-off exercise.

    Present the table to your finance people for approval.

    If they find some errors in the numbers - correct them manually.

    Index the table and use it in queries like this:

    SELECT [DayNoPerWeek]

    From dbo.FiscalCalendar

    where [Date] = ....

    Thanks for the tip. I'm wondering if there's there a way to improve the current query based on the fiscal year start date and the 4-4-5 or 4-5-4 style/leap year and update values in the current DimDate table? I could probably do this in Excel and import it but am trying to do everything in one script with minimum objects, if possible.

    I come from a non-profit background where the fiscal year ran from 7/1-6/30, but was otherwise equivalent to the calendar year. I'm not familiar with the 4-4-5 or 4-5-4 style of fiscal year. You should post sample data and expected results. I have a link in my signature that tells how to do that.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • I should have given a sample data, sorry!

    445 vs 454 = Represents how many weeks there are in 1st, 2nd, and 3rd month of each quarter.

    i.e.

    445 would indicate that in each quarter, 1st month would have 4 weeks, 2nd month would have 4 weeks, and 3rd month would have 5 weeks.

    454 would indicate that in each quarter, 1st month would have 4 weeks, 2nd month would have 5 weeks, and 3rd month would have 5 weeks.

    So here's the sample data.

    DECLARE @QrtStyle INT = 445 -- We are using 4 weeks + 4 weeks + 5 weeks quarter/month/week style in this example

    CREATE TABLE #TempDate ([Date] date, FiscalQtr INT, FiscalMo INT, FiscalMoofQrt INT, FiscalWk INT, FiscalWkofQtr INT, FiscalWkofMo INT,FiscalDay INT, FiscalDayofQrt INT, FiscalDayofMo INT, FIscalDayofWk INT)

    INSERT INTO #TempDate ([Date], FiscalQtr, FiscalMo, FiscalMoofQrt, FiscalWk, FiscalDay)

    SELECT '2016-08-01',1,1,1,1,1

    UNION

    SELECT '2016-08-02',1,1,1,1,2

    UNION

    SELECT '2016-08-03',1,1,1,1,3

    UNION

    SELECT '2016-08-04',1,1,1,1,4

    UNION

    SELECT '2016-08-05',1,1,1,1,5

    UNION

    SELECT '2016-08-06',1,1,1,1,6

    UNION

    SELECT '2016-08-07',1,1,1,1,7

    UNION

    SELECT '2016-08-08',1,1,1,2,8

    UNION

    SELECT '2016-08-09',1,1,1,2,9

    UNION

    --.....

    SELECT '2016-10-31',2,4,1,14,92

    UNION

    --.....

    SELECT '2016-12-05',2,5,2,19,127

    UNION

    --...

    SELECT '2017-03-27',3,CASE @QrtStyle WHEN 445 THEN 9 WHEN 454 THEN 8 END,CASE @QrtStyle WHEN 445 THEN 3 WHEN 454 THEN 2 END,35,239

    SELECT * FROM #TempDate

    DROP TABLE #TempDate

    Attached an image of desired output.The fields highlighted are the fields described above that I'm trying to calculate.

  • The 445 and 454 fiscal years only have 364 days. You have only given data for one fiscal year, so it's not clear how you are handling that extra day. In addition, I understand that using this system, some fiscal years have 53 weeks instead of 52. Under what circumstances does that occur?

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • o103452 (5/6/2016)


    I'm wondering if there's there a way to improve the current query

    What current query?

    _____________
    Code for TallyGenerator

  • Sergiy (5/9/2016)


    o103452 (5/6/2016)


    I'm wondering if there's there a way to improve the current query

    What current query?

    Ahh, I should have made the font for the script link bigger in the op.

    I'm using the script from the following website (Fiscal date query) - http://www.bradleyschacht.com/date-dimension-script-with-fiscal-year/#

  • drew.allen (5/9/2016)


    The 445 and 454 fiscal years only have 364 days. You have only given data for one fiscal year, so it's not clear how you are handling that extra day. In addition, I understand that using this system, some fiscal years have 53 weeks instead of 52. Under what circumstances does that occur?

    Drew

    I'll update this post after reviewing the data for 445/454 logic.

    Also, what do you mean by "under what circumstances does that occur?" w/e the year is a leap year, the script adds a week based on the variable. I'm trying to update the script (http://www.bradleyschacht.com/date-dimension-script-with-fiscal-year/#) where leap year logic is already implemented for the fields that it's currently calculating. The sample data/script is just a standalone example (full script is from the website - fiscal date script).

  • o103452 (5/10/2016)


    drew.allen (5/9/2016)


    The 445 and 454 fiscal years only have 364 days. You have only given data for one fiscal year, so it's not clear how you are handling that extra day. In addition, I understand that using this system, some fiscal years have 53 weeks instead of 52. Under what circumstances does that occur?

    Drew

    I'll update this post after reviewing the data for 445/454 logic.

    Also, what do you mean by "under what circumstances does that occur?" w/e the year is a leap year, the script adds a week based on the variable. I'm trying to update the script (http://www.bradleyschacht.com/date-dimension-script-with-fiscal-year/#) where leap year logic is already implemented for the fields that it's currently calculating. The sample data/script is just a standalone example (full script is from the website - fiscal date script).

    I mean exactly what I asked. You know the conditions, we do not. We could guess at the conditions, but it's much simpler and more consistent if you tell us what the conditions are.

    You also said that you were trying to modify the script, which indicates that the script does not work for you as it is currently written, but you haven't said how it's not working.

    I also suspect that adding an extra week every leap year is an oversimplification, because of the complexity of leap years. For instance, 2100 is not a leap year, but I suspect that you would want to add an extra week sometime between 2096 and 2104.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 12 posts - 1 through 11 (of 11 total)

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