CELKO (8/1/2012)

---

A useful idiom is a report period calendar. It gives a name to a range of dates.

CREATE TABLE Report_Periods

(report_name CHAR(10) NOT NULL PRIMARY KEY,

report_start_date DATE NOT NULL,

report_end_date DATE NOT NULL,

CONSTRAINT date_ordering

CHECK (report_start_date <= report_end_date),

etc);

These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. There can be gaps between them; we have to wait a year between each “Annual Going out Of Business Sale!” and there might be long stretches of time without any special sales. But we want to know their ranges so that the table is fairly constant once it is created.

I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL.

For you, I might use '[12][0-9][0-9][0-9]Q[1-4]' as the report period name. It will also help if you will learn to use the ISO-8601 date format. Using local dialects makes you look like a librarian who does not know Dewey Decimal 🙂 Fill out 50 or 100 years of data with a spreadsheet or text editor.