Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Tally Table Uses - Part I Expand / Collapse
Author
Message
Posted Monday, August 2, 2010 5:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 18, 2014 7:23 AM
Points: 40, Visits: 268

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.
Post #962535
Posted Monday, August 2, 2010 5:32 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:40 PM
Points: 2,755, Visits: 7,202
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
Post #962538
Posted Tuesday, August 3, 2010 6:50 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 36,794, Visits: 31,253
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #962750
Posted Tuesday, August 3, 2010 7:41 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:13 PM
Points: 33,100, Visits: 15,210
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
Post #962802
Posted Tuesday, August 3, 2010 7:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:40 PM
Points: 2,755, Visits: 7,202
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
Post #962814
Posted Tuesday, August 3, 2010 9:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 6, 2011 1:36 PM
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.
Post #962892
Posted Tuesday, August 3, 2010 9:09 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:40 PM
Points: 2,755, Visits: 7,202
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
Post #962898
Posted Tuesday, August 3, 2010 9:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 6, 2011 1:36 PM
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".
Post #962904
Posted Tuesday, August 3, 2010 9:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:40 PM
Points: 2,755, Visits: 7,202
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
Post #962910
Posted Tuesday, August 3, 2010 12:54 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 10, 2013 12:55 PM
Points: 28, Visits: 124
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
Post #963072
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse