Re: Simplifying db queries

"cruiserweight" <bayon86_at_yahoo.com> wrote in message news:1109902456.993304.254390_at_f14g2000cwb.googlegroups.com...
> Nope. I was wrong. On second look the first entry provided what is
> probably a decent explanation. My db skills, however, are no where near
> the level. I guess I'm effed.
>

Sorry I don't have a web page link for you, but I hope I can help.

A "Calendar Table" is one of the classic ways to get into creating "dimension tables", one of two kinds of tables that are used in "star schemas".

With a mere 3652 rows, you can have one row for each day for ten years. Of course, the first column is the day's date, in whatever format the rest of the data uses for dates. That's the primary key. Associated with each day, you can have a column that says what "reporting week" it belongs to, (maybe 1 to 52 or 53). What "Reporting month it belongs to (1 to 12), and so on. Whether it's a company holiday or not, etc. etc.

It's not too hard to create an almanac program that will fill in all these values, once you know the rules for the enterprise.

Then, later on, when reporting time comes along, it's not hard to join this with data that has a date column, and limit the output to data whose date has CALENDAR.MONTH = 3 or somethnig like that.

This is too tersely stated, but the technique is well understood, and yields good results. Received on Fri Mar 04 2005 - 21:17:32 CET