Click here to monitor SSC
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
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 1065

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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 8067
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-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44994 Visits: 39880
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36062 Visits: 18736
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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 8067
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
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 8067
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
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 8067
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 (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 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
   Wink,
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
   Wink,
FP
   (
      FiscalDate, FiscalWeekDay, FiscalPeriod, FiscalWeek, FiscalQuarter
   Wink
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
   Wink
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