SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tally Table Uses - Part I


Tally Table Uses - Part I

Author
Message
Smendle
Smendle
Old Hand
Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)

Group: General Forum Members
Points: 373 Visits: 1073

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.
Sioban Krzywicki
Sioban Krzywicki
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1865 Visits: 8090
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87258 Visits: 41113
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

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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63468 Visits: 19115
Stefan's updates have been published for the article.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Sioban Krzywicki
Sioban Krzywicki
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1865 Visits: 8090
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
SQL Curious
SQL Curious
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 29
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.
Sioban Krzywicki
Sioban Krzywicki
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1865 Visits: 8090
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
SQL Curious
SQL Curious
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 29
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".
Sioban Krzywicki
Sioban Krzywicki
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1865 Visits: 8090
jim.jaggers1 (8/3/2010)
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".


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
Micah Ritchie
Micah Ritchie
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 125
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. :-D


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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search