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 Saturday, July 31, 2010 1:28 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:25 PM
Points: 2,784, Visits: 7,297
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
Post #961796
Posted Sunday, August 1, 2010 9:56 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:14 PM
Points: 37,107, Visits: 31,665
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.

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

(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 #961978
Posted Monday, August 2, 2010 6:17 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:14 PM
Points: 37,107, Visits: 31,665
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."

(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 #962118
Posted Monday, August 2, 2010 6:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:25 PM
Points: 2,784, Visits: 7,297
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.

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
Post #962130
Posted Monday, August 2, 2010 6:44 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:25 PM
Points: 2,784, Visits: 7,297
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
Post #962136
Posted Monday, August 2, 2010 6:44 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:25 PM
Points: 2,784, Visits: 7,297
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.

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
Post #962137
Posted Monday, August 2, 2010 7:12 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:14 PM
Points: 37,107, Visits: 31,665
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."

(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 #962166
Posted Monday, August 2, 2010 7:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:25 PM
Points: 2,784, Visits: 7,297
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
Post #962177
Posted Monday, August 2, 2010 8:29 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 2:42 PM
Points: 33,278, Visits: 15,447
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
Post #962237
Posted Monday, August 2, 2010 8:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:25 PM
Points: 2,784, Visits: 7,297
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
Post #962255
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse