Totals by Week

Transact-SQL
21

Since you like giving me programmign assignments, would you post code to compute "the whole shebang" for the Student’s T-distribution? The formula and a look-up are at:

Now, do not use a look-up table! Just use your proprietary T-SQL functions.

The reason I mention the Student T distribution is that I started life as a FORTRAN programmer doing stats. When I needed the student T, I actually had written a subroutine for it! So when I got to SQL, I wanted to keep my old paradigm. I tried to copy my subroutine into T-SQL!

This was a bad idea. SQL is not a computational language (for mathematics or for temporal).

Somewhere around here, I have a CD with the calendar table, written in ANSI/ISO standard DDL and insert statements. When I go on a job, I do not care if it is T-SQL, DB2, Oracle, PostGres, Teradata, Informix, or an SQL product I have never worked on before in my life.

I create and load a calendar table, and a series table and perhaps some other things (like the Student T) for that job.

Initially I was worried about using too much storage.:worried: Today, 1 million rows in the table is nothing and primary storage is larger than the secondary storage we had when I started.

22

Nope. Not posting code to generate the "Student T" table because 1) I haven't been talking about it like you've been talking about the Calendar Table and 2) as you advertised in your article, even you don't know what the symbology of the formula you posted is. If I were tasked to work with such a thing, I'd make it my business to understand the symbology and then I'd likely write a formula in T-SQL to solve for it and embed it in the equivalent of an iSF (a high performance inline Table Valued Function that returns a scalar value), which would probably use proprietary code to simplify it and produce better performance with less resource usage. :wink: And, if I were in the business of working with such a formula across multiple platforms, I would write it specifically to use the wonderful proprietary extensions that enrich the capabilities of each engine for the same reasons.

Heh... and no... I'm not giving you tasks. I'm asking you to support what you've written of. You say you use Calendar Tables (sometimes a good idea) but I've not seen code from you that will generate such a thing be it ANSI/ISO/Portable/whatever or a reference link to an article where you or anyone else has explained it. Rather, you've referred us to a site where copy'n'paste one bloody year at a time is required.

23

I think you are missing my point about a table lookup versus trying to use a non-computational, simple, compiler to do computational work. SQL is not a computational language; it is a data language. I want to go out and find an appropriate tool for him computations, use it once, put it on a CD, and carry it from job to job.

Why repeat constants? Going back to my old days as a FORTRAN programmer, there was actually an IBM Fortran manual that defined the CONSTANT feature with an example at the start of the manual of "CONSTANT PI = 3.141592653" so that if the value of pi changes, you could simply you could simply change this one statement and update your program. No, this is really true.

Most people who have never done a lot of applied math, do not know that modern calculators have lookup tables built into their chips. The only computations done for complex trig functions, etc. are first and second differences (this is part of finite differences math, but nobody teaches it anymore).

Yes I can write a create sequence statement, use ADDDATE() with the current timestamp, and generate a list of dates with an ordinal date number to build a quick calendar table. If I decide to be a cowboy coder and write only dialect I still know how to do a while loop in T SQL (or SQL/PSM).

But why would I do that in the real world? I want to talk to the accounting department. I want to know what the fiscal calendar for the client looks like. I want to know their holidays (which can be pretty weird, actually).

Usually 100 year calendar table is more than enough. That means 36525 rows in the table. This is tiny! In SQL Server, The DATE datatype is only three bytes, so if you add 100 bytes of other data to the rows, this table will fit into primary storage on a modern computer.

My suggested source data for the week_date might be one year at time, but so what? I can can hit a key to repeat the macro 10, 20 or 50 times easily.

24

And you have missed mine. To the best of my knowledge, you've never posted code that demonstrates how to generate a full calendar table and certainly not a 100 year calendar table but you keep talking about their uses. Having people screen scrape one screen per year for even 10 years of calendar table is ridiculous.

I'll also tell you that there are reasons why you might want to avoid a Calendar Table. For example, if you have a system that's got memory problems, then you might want to generate just what you need for a Calendar Structure as a CTE (Subquery Factoring in Oracle and part of the SQL-99 standard) or other derived table instead of suffering all of the logical reads you might suffer with a physical calendar table. And, no... even a While loop won't be portable because as soon as you declare any type of variable, you've destroyed portability between at least Oracle and SQL server. True portability is a myth except for the simplest of things like certain types of CRUD but even ORMs embrace the differences between database engines.

I also think it odd that the king of ANSI/ISO/Portable code won't offer code to generate such a thing. Is it because it cannot be made truly portable across the myriad of database engines that are out there today?

Also, I could be mistaken but I believe you'll find that ADDDATE() is a proprietary synonym for DATE_ADD() in MySQL, neither of which are the ANSI Standard for DATEADD() (and MySQL doesn't have it that way) further proving that true portability is a myth and requires supposed "cowboy" coders to put up with non-ANSI extensions all the time and find other methods including those that are proprietary to the database engines they working on.