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
Sioban Krzywicki
Sioban Krzywicki
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1337 Visits: 8081
Comments posted to this topic are about the item Tally Table Uses - Part I

--------------------------------------
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 (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55732 Visits: 40408
It IS interesting to see what folks do with the Tally Table or the "pseudo cursor" methods that are used to build it.

Nice article and lot's of code to play with. Thanks. Now, I have to go back and play with the code along the way. I'm sometimes amazed at what some accountants/companies will go through in the name of a fiscal year. I have to admit that your requirements are fairly odd although quite easy to understand.

Thanks for all the kudos on the Tally Table article, Stefan. I'm glad I could help and it's good to see so many folks carrying such a useful technique forward. Heh... not sure I deserve all that, though... lot's of folks were using a "numbers" table a very long time before my article. :-P

Looking forward to Part 2...

--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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55732 Visits: 40408
Heh... pretty low marks for the article so far, Stefan. Although I certainly appreciate what you tried to do both in your code and for the Tally Table, I think that people read "Tally Table Uses" as an article title and expect to see a plethora of code snippets for different purpose uses of the Tally Table across a wide spectrum. Instead, they find a fairly peculiar calendar table with a very limited use (one company) which also requires manual intervention every couple of years that just happens to use a Tally table as a driver. Again, I appreciate both attempts but the public apparently has a different take on the article. I'm telling you this because you may want to reconsider what you publish as Part 2. Give it a shot...

--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
Sioban Krzywicki
Sioban Krzywicki
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1337 Visits: 8081
Jeff Moden (8/1/2010)
It IS interesting to see what folks do with the Tally Table or the "pseudo cursor" methods that are used to build it.

Nice article and lot's of code to play with. Thanks. Now, I have to go back and play with the code along the way. I'm sometimes amazed at what some accountants/companies will go through in the name of a fiscal year. I have to admit that your requirements are fairly odd although quite easy to understand.

Thanks for all the kudos on the Tally Table article, Stefan. I'm glad I could help and it's good to see so many folks carrying such a useful technique forward. Heh... not sure I deserve all that, though... lot's of folks were using a "numbers" table a very long time before my article. :-P

Looking forward to Part 2...


Perhaps they were using it before you, but you're where I learned it so you get the 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
Sioban Krzywicki
Sioban Krzywicki
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1337 Visits: 8081
Jeff Moden (8/2/2010)
Heh... pretty low marks for the article so far, Stefan. Although I certainly appreciate what you tried to do both in your code and for the Tally Table, I think that people read "Tally Table Uses" as an article title and expect to see a plethora of code snippets for different purpose uses of the Tally Table across a wide spectrum. Instead, they find a fairly peculiar calendar table with a very limited use (one company) which also requires manual intervention every couple of years that just happens to use a Tally table as a driver. Again, I appreciate both attempts but the public apparently has a different take on the article. I'm telling you this because you may want to reconsider what you publish as Part 2. Give it a shot...


If they just don't find it useful, I don't mind so much. There are some good techniques (I think) in there for any kind of non-standard calendar and it only requires manual intervention if the calendar is changed by the business people. I put that part in to show how easy it is to make that kind of manual intervention. I thought the subtitle was pretty clear that article I only handles fiscal year calculations.

Where I'd worry is if people started tearing the code apart, telling me I got things wrong, then I'd have to go back and reexamine things and fix things. Even then, I wouldn't so much "mind" as be chagrined. I do appreciate the suggestion, but I'm going to let it stand. I can't count the number of times I've read code or a coding technique and thought I'd never use it and later found how useful it was.

--------------------------------------
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
Sioban Krzywicki
Sioban Krzywicki
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1337 Visits: 8081
Jeff Moden (8/1/2010)
It IS interesting to see what folks do with the Tally Table or the "pseudo cursor" methods that are used to build it.

Nice article and lot's of code to play with. Thanks. Now, I have to go back and play with the code along the way. I'm sometimes amazed at what some accountants/companies will go through in the name of a fiscal year. I have to admit that your requirements are fairly odd although quite easy to understand.

Thanks for all the kudos on the Tally Table article, Stefan. I'm glad I could help and it's good to see so many folks carrying such a useful technique forward. Heh... not sure I deserve all that, though... lot's of folks were using a "numbers" table a very long time before my article. :-P

Looking forward to Part 2...


And before I forget, thanks for the compliments above.

--------------------------------------
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 (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55732 Visits: 40408
Stefan Krzywicki (8/2/2010)
I do appreciate the suggestion, but I'm going to let it stand. I can't count the number of times I've read code or a coding technique and thought I'd never use it and later found how useful it was.


I have a great appreciation for that and my hat is off to you for making such a commitment to others. Well done!

Like I said, I look forward to Part 2... even the original one. :-)

--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
Sioban Krzywicki
Sioban Krzywicki
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1337 Visits: 8081
Jeff Moden (8/2/2010)
Stefan Krzywicki (8/2/2010)
I do appreciate the suggestion, but I'm going to let it stand. I can't count the number of times I've read code or a coding technique and thought I'd never use it and later found how useful it was.


I have a great appreciation for that and my hat is off to you for making such a commitment to others. Well done!

Like I said, I look forward to Part 2... even the original one. :-)


Well, it'll have to be the original once since it is running tomorrow : -)

--------------------------------------
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
Steve Jones
Steve Jones
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: Administrators
Points: 42596 Visits: 18877
I thought this was interesting, and I've dealt with this in a few places. If you don't have to work with the financial system, you might not encounter this. It seems CPAs always want things to end on a Friday, or some off period, not just letting things end on whatever day the month ends.

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.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1337 Visits: 8081
Steve Jones - Editor (8/2/2010)
I thought this was interesting, and I've dealt with this in a few places. If you don't have to work with the financial system, you might not encounter this. It seems CPAs always want things to end on a Friday, or some off period, not just letting things end on whatever day the month ends.



Thanks. As a consultant, I've seen this kind of table at a number of places and they're all different. Worse, most have the dates either updated by hand or in Excel and then imported.

I likely should have pointed out in the article one of the reasons I listed this as a Tally Table article instead of just a Fiscal Period article. When I dealt with this kind of think before Tally Tables, it always came down to cursors and there's a big change in how you think about the problem with a Tally Table. With a cursor, you think about looping through the data and start setting up variables for incrementing the differenct results you'll need. Then you set your maximum values for the various types: quarters, periods, etc... Not only does it run slower, it becomes quite a bit to keep track of and it isn't easy to change when the inevitable requests come in to tweak it a bit.

With the tally table, the variables go away and you think about math. Create a formula for each column and go! When the change requests come in, you either tweak the formula or, if it is a one-off, you do as I did in the article, change the start points and everything zooms along.

The switch in thinking probably saves me more time than the improvement in run speed. And now I have code I can bring with me from assignment to assignment that's actually worth bringing.

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