May 5, 2016 at 4:15 pm
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!
May 5, 2016 at 4:42 pm
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
May 5, 2016 at 6:52 pm
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
May 6, 2016 at 8:10 am
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.
May 6, 2016 at 8:18 am
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.
May 6, 2016 at 8:43 am
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
May 9, 2016 at 12:37 pm
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.
May 9, 2016 at 2:43 pm
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
May 9, 2016 at 4:05 pm
o103452 (5/6/2016)
I'm wondering if there's there a way to improve the current query
What current query?
_____________
Code for TallyGenerator
May 10, 2016 at 7:57 am
Sergiy (5/9/2016)
o103452 (5/6/2016)
I'm wondering if there's there a way to improve the current queryWhat 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/#
May 10, 2016 at 8:03 am
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).
May 10, 2016 at 8:42 am
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