Tally Table Uses - Part I

  • 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.

  • 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

  • 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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Stefan's updates have been published for the article.

  • 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

  • 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.

  • 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

  • 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".

  • 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

  • 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

  • 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