Tally table question

Question

Tally table question

lnoland

SSCommitted

Points: 1753
More actions
May 11, 2012 at 10:53 am

#257872

I have been happily using Tally tables to good effect since I first read about them here (Thanks!), but there is a usage question which comes up now and again and I was wondering how some of you seasoned veterans of Tally Table usage generally deal with the situation.
Basically, most of the Tally Table examples I have seen start the tables at 1. In most cases that seems to work fine but there are some cases where the inclusion of 0 seems like a useful thing. To be sure, if there's a 0 there, for some things where 0 is not appropriate it may be necessary to ensure it is explicitly excluded where the table is used, but the alternative would seem to be that where the zero is needed in, say, a join, but the tally table starts at 1, then something like "x=tally-1" becomes necessary and though that works, that doesn't strike me as more desirable than just including the zero in the table.
What is your take on this? Do you generally always have the Tally table start at 1 and just make adjustments as necessary or do you tailor the Tally table to the problems to which it is being applied and make the range whatever is needed for those problems? Do you have specific reasons why you do it one way or the other or did you just make an arbitrary choice and stick with it?
Thanks -- I look forward to hearing your opinions.
- Les

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

others will certainly do it differently than I do, but I have three different tables for accomplishing Tally-related objectives:

Tally, which starts with 1,

TallyCalendar, which is all date related.

and Numbers, which starts at 0.

so if i'm writing something that needs to join to zero, i use the Numbers table (instead of Tally.N -1) just for convenience sake.

also, for me, the code jumps out a little more; if i join to Numbers, i know it's because of zero.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

lnoland SSCommitted Points: 1753 More actions · Answer 2

Lowell (5/11/2012)
others will certainly do it differently than I do, but I have three different tables for accomplishing Tally-related objectives:
Tally, which starts with 1,
TallyCalendar, which is all date related.
and Numbers, which starts at 0.
so if i'm writing something that needs to join to zero, i use the Numbers table (instead of Tally.N -1) just for convenience sake.
also, for me, the code jumps out a little more; if i join to Numbers, i know it's because of zero.

Well, that certainly covers the bases as far as convenience is concerned, and I like the reasoning behind the usages. Of course, if I were to do that, unless they were fairly small tables I would probably have someone complaining about the duplicate data coverage. (On the other hand, if that was really a concern, I'll bet that making Tally a view based on Numbers would be a simple enough implementation that the query plans for either one would be pretty near identical).

Thanks for sharing.

- Les