In my previous life at Papa Johns Corporate, we had fiscal years that did not necessarily match calendar years. To keep track of what date cycles we were in we generated a calendar table. The calendar table had a variety of data stored within it, such as :

* Period
* Start and end of period
* Day of period and days remaining in period
* etc.

Additionally, we threw in a counter field that simply provided an incremental number that could be leveraged for joining and filling in empty rows when joining unlike tables, or for any number of other occassions where you needed a list of sequential numbers.

I brought that table with me when I joined my current employer, and to this date we still use it for variety of efforts as well.

Recently I stumbled on a simple query that provided a list of dates :

SELECT TO_DATE (:x, 'DD-MON-YYYY') + LEVEL - 1
FROM DUAL
CONNECT BY LEVEL <= :n;

Where x is a string formatted date e.g. 03-JAN-2009 and n is number of days in range. Submitting '14-MAY-2009' and 5 would return 5 rows of data beginning with 14-May-2009 and ending with 18-May-2009.

I gave it some thought and wondered if I could convert our existing CAL_TABLE to a dynamic on-the-fly SQL query instead...

Pages: 1· 2