August 2, 2010 at 5:26 pm
The real answer here is that it doesn't really matter if a Tally table contains 10,000 rows or a million rows. It it's used properly and if it has the correct clustered index (an omission in this article which needs to be fixed), it's just not going to matter how big a Tally table is. The only reason why I keep mine small (11k rows) is because I almost never need something larger and it always fits in even the smallest cache on most machines. Any smaller than 8k and you have to check that VARCHAR splits (and the like) won't overrun the Tally table.
So far as the word "inversly" goes here, I'm not sure what you mean by the way you used the word.
--Jeff Moden
Well I spelled inversly wrong I meant inversely, which is to say that given 2 seperate distinct objects (for lack of a better description) I could create a tally table for either object in his case tally table the dates or tally table the Y W D values and then create a query against whichever tally table was created.
August 2, 2010 at 5:32 pm
Smendle (8/2/2010)
The real answer here is that it doesn't really matter if a Tally table contains 10,000 rows or a million rows. It it's used properly and if it has the correct clustered index (an omission in this article which needs to be fixed), it's just not going to matter how big a Tally table is. The only reason why I keep mine small (11k rows) is because I almost never need something larger and it always fits in even the smallest cache on most machines. Any smaller than 8k and you have to check that VARCHAR splits (and the like) won't overrun the Tally table.
So far as the word "inversly" goes here, I'm not sure what you mean by the way you used the word.
--Jeff Moden
Well I spelled inversly wrong I meant inversely, which is to say that given 2 seperate distinct objects (for lack of a better description) I could create a tally table for either object in his case tally table the dates or tally table the Y W D values and then create a query against whichever tally table was created.
Well, the Tally Table itself is just a single column int table of consecutive integers. I used it to create the Fiscal Dates table. You could use it to create either of those date tables, but it itself is just a numbers column.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 3, 2010 at 6:50 am
Smendle (8/2/2010)
The real answer here is that it doesn't really matter if a Tally table contains 10,000 rows or a million rows. It it's used properly and if it has the correct clustered index (an omission in this article which needs to be fixed), it's just not going to matter how big a Tally table is. The only reason why I keep mine small (11k rows) is because I almost never need something larger and it always fits in even the smallest cache on most machines. Any smaller than 8k and you have to check that VARCHAR splits (and the like) won't overrun the Tally table.
So far as the word "inversly" goes here, I'm not sure what you mean by the way you used the word.
--Jeff Moden
Well I spelled inversly wrong I meant inversely, which is to say that given 2 seperate distinct objects (for lack of a better description) I could create a tally table for either object in his case tally table the dates or tally table the Y W D values and then create a query against whichever tally table was created.
Heh... nah... wasn't even concerned about the misspelling. But now I understand your statement...
You gain nothing by having a Tally Table smaller than 8K. In 2k5, if I need something larger than my standard Tally Table of 11k rows (more than 30 years worth of days), then I'll use a cross join on the Tally Table or some "Itzek" cascading CTE's to make one on the fly. Some folks just create a Tally Table with a million rows (no real performance disadvantage for proper code) and call it a day. There IS a performance DISADVANTAGE of combining a Tally Table with date information because it decreases the row density of the Tally Table.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2010 at 7:41 am
Stefan's updates have been published for the article.
August 3, 2010 at 7:49 am
Thanks Steve.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 3, 2010 at 9:04 am
Stefan,
Great article. I always enjoy seeing the set based alternatives to the iterative approach.
And, I love the math. I think I have a more intuitive approach to the period calculation you provided as:
CASE
WHEN FiscWeek%13 BETWEEN 1 and 5 THEN ((FiscQuarter - 1) * 4) + 1 - (FiscQuarter - 1)
WHEN FiscWeek%13 BETWEEN 5 and 9 THEN ((FiscQuarter - 1) * 4) + 2 - (FiscQuarter - 1)
WHEN FiscWeek%13 > 9 THEN ((FiscQuarter - 1) * 4) + 3 - (FiscQuarter - 1)
WHEN FiscWeek%13 = 0 THEN ((FiscQuarter - 1) * 4) + 3 - (FiscQuarter - 1) END as Period
If you consider that each quarter contains 3 periods - numbered 1, 2, and 3 - and the purpose of your math is to shift the numbering scheme from quarter to year, then you will want a number scheme like the following
Qtr-Pd # in Qtr Yr-Pd # in Q1 Yr-Pd # in Q2 Yr-Pd # in Q3 Yr-Pd # in Q4
1 1 4 7 10
2 2 5 8 11
3 3 6 9 12
Then the pattern emerges that each Yr-Pd # is the (Qtr-Pd #) + (wks in Pd)*(Pd # - 1) or
CASE WHEN FiscWeek%13 BETWEEN 1 and 5 THEN 1+3*(FiscQuarter - 1)
WHEN FiscWeek %13 BETWEEN 6 and 9 THEN 2+3*(FiscQuarter - 1)
WHEN FiscWeek %13 > 9 THEN 3+3*(FiscQuarter - 1)
WHEN FiscWeek %13 = 0 THEN 3+3*(FiscQuarter - 1)
END
Which is algebraically equivalent to your math; but, I think, more intuitive logic.
August 3, 2010 at 9:09 am
jim.jaggers1 (8/3/2010)
Stefan,Great article. I always enjoy seeing the set based alternatives to the iterative approach.
And, I love the math. I think I have a more intuitive approach to the period calculation you provided as:
CASE
WHEN FiscWeek%13 BETWEEN 1 and 5 THEN ((FiscQuarter - 1) * 4) + 1 - (FiscQuarter - 1)
WHEN FiscWeek%13 BETWEEN 5 and 9 THEN ((FiscQuarter - 1) * 4) + 2 - (FiscQuarter - 1)
WHEN FiscWeek%13 > 9 THEN ((FiscQuarter - 1) * 4) + 3 - (FiscQuarter - 1)
WHEN FiscWeek%13 = 0 THEN ((FiscQuarter - 1) * 4) + 3 - (FiscQuarter - 1) END as Period
If you consider that each quarter contains 3 periods - numbered 1, 2, and 3 - and the purpose of your math is to shift the numbering scheme from quarter to year, then you will want a number scheme like the following
Qtr-Pd # in Qtr Yr-Pd # in Q1 Yr-Pd # in Q2 Yr-Pd # in Q3 Yr-Pd # in Q4
1 1 4 7 10
2 2 5 8 11
3 3 6 9 12
Then the pattern emerges that each Yr-Pd # is the (Qtr-Pd #) + (wks in Pd)*(Pd # - 1) or
CASE WHEN FiscWeek%13 BETWEEN 1 and 5 THEN 1+3*(FiscQuarter - 1)
WHEN FiscWeek %13 BETWEEN 6 and 9 THEN 2+3*(FiscQuarter - 1)
WHEN FiscWeek %13 > 9 THEN 3+3*(FiscQuarter - 1)
WHEN FiscWeek %13 = 0 THEN 3+3*(FiscQuarter - 1)
END
Which is algebraically equivalent to your math; but, I think, more intuitive logic.
Nice, I'll give it a try. Thanks!
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 3, 2010 at 9:14 am
And, a question on type casting. I notice you use explicit type casting with
Cast(Ceiling(Cast(YearDay as real)/Cast(7 as real))as int)
and similar functions. I think the same result would be accomplished with the implicit typecasting of
ceiling(YearDay/7.0)
and be a little easier to read.
Of course I am not a "real" programmer as I write code to do my job rather than as my job (though I'm considering a career change from Data Analysis to DB Developer). So it may be that real programmers find it better to explicitly control the data type rather than trusting to implicit data typing "rules".
August 3, 2010 at 9:18 am
jim.jaggers1 (8/3/2010)
And, a question on type casting. I notice you use explicit type casting withCast(Ceiling(Cast(YearDay as real)/Cast(7 as real))as int)
and similar functions. I think the same result would be accomplished with the implicit typecasting of
ceiling(YearDay/7.0)
and be a little easier to read.
Of course I am not a "real" programmer as I write code to do my job rather than as my job (though I'm considering a career change from Data Analysis to DB Developer). So it may be that real programmers find it better to explicitly control the data type rather than trusting to implicit data typing "rules".
I tend to prefer it because it eliminates a point of failure. Also if someone else uses the code in the future and I'm not explaining it to them and I haven't commented that particular piece (I should, I know), they won't be tempted to get rid of the "pointless" .0 and end up with errors that seem inexplicable.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 3, 2010 at 12:54 pm
Stefan,
First I would like to say, great Article. I did notice an issue though. As this is a standard ISO 544 Financial Calendar (544 refers to the week count of each period in a quarter), the last week of the first period in a quarter can include the first week of the next month. This causes the calculation of the Fiscal Year to glitch. Take a look at what happens around 03/01/2009 for an example.
Case
WHEN DATEPART(MM,FiscalDate) > FiscalPeriod
THEN DATEPART(YYYY, FiscalDate) + 1
ELSE DATEPART(YYYY, FiscalDate)
END AS FiscalYear
Simply checking that the Fiscal Period also is equal to 12 will fix this:
Case
WHEN (DATEPART(MM,FiscalDate) > FiscalPeriod) and (FiscalPeriod = 12)
THEN DATEPART(YYYY, FiscalDate) + 1
ELSE DATEPART(YYYY, FiscalDate)
END AS FiscalYear
And, in keeping with the spirit of the CTE:
Declare @StartDate as Date = '12/31/2008';
WITH
YearDays (YearDay, N)
AS
(
select top 3640
CASE Tally.N%364
WHEN 0 THEN 364
ELSE N%364
END AS YearDay, N
FROM Tally
),
FWQ (FiscalWeek, FiscalQuarter, YearDay, N)
AS
(
Select CAST(CEILING(Cast(YearDay as Real)/CAST(7 as Real)) AS INT) as FiscalWeek,
CEILING(Cast(YearDay as Real)/CAST(91 as real)) as FicalQuarter, YearDay, N
FROM YearDays
),
FP
(
FiscalDate, FiscalWeekDay, FiscalPeriod, FiscalWeek, FiscalQuarter
)
AS
(
SELECT
Cast(DATEADD(DD, N, @StartDate) as DATE) AS FiscalDate,
CASE DATEPART(DW, DATEADD(dd, N, @StartDate))
WHEN 1 THEN 7
WHEN 2 THEN 1
WHEN 3 THEN 2
WHEN 4 THEN 3
WHEN 5 THEN 4
WHEN 6 THEN 5
WHEN 7 THEN 6
END as FiscalWeekDay,
CASE
WHEN FiscalWeek%13 BETWEEN 1 AND 5
THEN ((FiscalQuarter-1) * 4) + 1 -(FiscalQuarter-1)
WHEN FiscalWeek%13 BETWEEN 6 and 9
THEN ((FiscalQuarter-1) * 4) + 2 -(FiscalQuarter-1)
WHEN FiscalWeek%13 > 9
THEN ((FiscalQuarter-1) * 4) + 3 -(FiscalQuarter-1)
WHEN FiscalWeek%13 = 0
THEN ((FiscalQuarter-1) * 4) + 3 -(FiscalQuarter-1)
END As FiscalPeriod,
FiscalWeek, FiscalQuarter
FROM Fwq
)
SELECT
FP.*,
Case
WHEN (DATEPART(MM,FiscalDate) > FiscalPeriod) and (FiscalPeriod = 12)
THEN DATEPART(YYYY, FiscalDate) + 1
ELSE DATEPART(YYYY, FiscalDate)
END AS FiscalYear
FROM FP
This can then be dropped into an inline function, moving the @StartDate declaration into args for the function. That gives you an easy way to rebuild your table, or even just use the function as the source for ad-hoc queries when the Financial guys ask for some obscure data as then tend to do. 😀
What I find really great about your code though is that a simple adjustment of the Case statement for the Fiscal Period allows this to also handle the other common ISO Calendars (the 454 and 445). This make for a very flexible piece of code, with very little editing.
Again, thanks for the great article, I'm going to replace some of what I have been using with this, because it greatly simplifies Fiscal Calendar creation.
Micah Ritchie
August 3, 2010 at 3:29 pm
Nice article. Thanks for sharing. Now it is time to play with the scripts.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply