asked on

FM - How to Create a Dates Table

Beginning to realize that I am having to create too many table relationships between dates, so what is the best practice for creating a "Dates Table"? Would you create a table for years and then a subtable for months and a subtable for days in order to normalize?

Will Loving

Need some more info about what you are trying to do. I've created tables for "excluded" dates, to be referenced so that events are scheduled on known holidays or other days a business is closed, but I'm not sure what your trying to achieve with a Dates table. Perhaps your existing relationships could be handled in a more efficient way.

rvfowler2

ASKER

See attached.
--DatesTable.JPG
--DatesTable2.JPG

rvfowler2

ASKER

The fields that end with "formatted" are formatted for a weekly email. Boss doesn't want a report or pdf.

--DatesTable3.JPG

North2Alaska

I use a table called Calendar that breaks down each day with the many values needed for dates. As you will see in the list below that there are many parts to a date and can be used in many ways. The FY_XXXX columns are for Fiscal Year calculations. For example, if I want to find the dates (or days of the week) for a given week of the year, I can look at WEEK_KEY (the data is something like 201112 for the 12 week of 2011). Add in the WEEK_DAY_FLAG (Y or N) and now I can see what the monday thru friday dates are. There are many ways to use a table like this and there are many ways to populate it. My current table goes from 1989 to 2090 and would be a trivial thing to add more years.

This is a list of columns in my table:
DATE_KEY
DATE_DATE
DAY_NAME
DAY_IN_WEEK_NBR
DAY_IN_MONTH_NBR
DAY_IN_QUARTER_NBR
DAY_IN_YEAR_NBR
DAY_BEGIN_DATE
DAY_END_DATE
WEEK_DAY_FLAG
WEEK_KEY
WEEK_IN_MONTH_NBR
WEEK_IN_QUARTER_NBR
WEEK_IN_YEAR_NBR
WEEK_BEGIN_DATE
WEEK_END_DATE
MONTH_KEY
MONTH_NAME
MONTH_IN_QUARTER_NBR
MONTH_IN_YEAR_NBR
MONTH_DURATION
MONTH_BEGIN_DATE
MONTH_END_DATE
QUARTER_KEY
QUARTER_IN_YEAR_NBR
QUARTER_DURATION
QUARTER_BEGIN_DATE
QUARTER_END_DATE
YEAR_KEY
YEAR_DURATION
YEAR_BEGIN_DATE
YEAR_END_DATE
FY_WEEK_KEY
FY_WEEK_IN_MONTH_NBR
FY_WEEK_IN_QUARTER_NBR
FY_WEEK_IN_YEAR_NBR
FY_WEEK_DURATION
FY_WEEK_BEGIN_DATE
FY_WEEK_END_DATE
FY_MONTH_KEY
FY_MONTH_IN_QUARTER_NBR
FY_MONTH_IN_YEAR_NBR
FY_MONTH_DURATION
FY_MONTH_BEGIN_DATE
FY_MONTH_END_DATE
FY_QUARTER_KEY
FY_QUARTER_IN_YEAR_NBR
FY_QUARTER_DURATION
FY_QUARTER_BEGIN_DATE
FY_QUARTER_END_DATE
FY_YEAR_KEY
FY_YEAR_DURATION
FY_YEAR_BEGIN_DATE
FY_YEAR_END_DATE

Will Loving

Nicely done N2A.

rvfowler2

ASKER

By columns, I'm assuming you also mean they are fields. However, using your system, did you create a record for every date (day) from 1989 to 2090?

North2Alaska

Yes, column and field are the same in FM. I built the table with a SQL Script. It took some deep thought... :-D

rvfowler2

ASKER

But my question was do you have a separate record for every date from 1989 to 2090? If so, why didn't you use subtables for months and then days?

rvfowler2

ASKER

Oh, and since the data field is never the same, do you use that as your primary key or do you just create the normal PK numbered 1 on up?

ASKER CERTIFIED SOLUTION

North2Alaska

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rvfowler2

ASKER

Looks like exactly what I want though I'll have to splash around a bit to figure it out. Do you name your QUARTER_KEY 1, 2, 3, or 4 and repeat that for every record it applies to, or do you give it a unique number based on year and quarter?

rvfowler2

ASKER

Thanks, don't know SQL, but could do it in Excel with calcs and then drag down about 3,000 rows.

North2Alaska

Quarter key includes the year, so it might look like 20104 or 19991.

Do you have a need for a fiscal year as well? I could send you output from our table, but we have a fiscal year that starts in Oct. That may not work for you.

Will Loving

Should be easy enough to run a few replace calculations to convert the Quarters to calendar years. Also, the Quarter fields can simply be stored calculations that you can modify as required.

North2Alaska

Avoid calculations if you can. Remember we want to have the fastest query as we can. Calcs require processing, etc...

Here's my Calendar table less the FY_XXX columns and includes the data.

calendar.fp7

Will Loving

Calcs only require processing if they are unstored. My understanding is that a stored/indexed calc would be negligibly slower than a static field. Something as simple as

Month_Name = MonthName( Date )

Shouldn't be measurably slower than storing the value directly. Maybe I'm wrong about this but I've never seen anything FM test to indicate otherwise. Similarly for virtually all the other fields....

rvfowler2

ASKER

Yes, I believe stored fields only calc under certain situations. I guess you use them if you have fairly static data. Also, we have 3 different fiscal years for taxes based on the county.

rvfowler2

ASKER

N2A, the file is completely awesome (can you tell I'm from So Calif originally?). However, I must be having a dense moment because I can't figure out how to use it. I want to total all Lead records and chart them for each week. However, all I know how to do is make a copy of my Leads TO and join it to itself by the calc field that has Year ( DateCreation ) & WeekofYear (DateCreation). But how would you doing it using your table?

North2Alaska

Can you provide a sample database? and a report description? I'll put something together.

As the points are moderately important, a new question might be in order... :-)