In some environments, developers have to maintain a master “dates” table with a complete list of dates. If used correctly, it can allow a report to fill in date gaps where there are no transactions. You would have the report start with the master dates table and then link to the date field in your transaction table using a Left Outer Join. The challenge is maintaining the master dates table with current dates.

Today, Adam Butt of APB reports (Norway) helped me create a SQL View (.SQL) for SQL Server  that generates a master dates table. It is based on the SQL for an inflation table (version B)  submitted last week by Michael Hurwood, combined with the SQL DateAdd() function. He also created a second SQL View (.SQL) for Oracle.

I see two ways to deploy this.

1) Run Adam’s .SQL file in your database to save a view. You can adjust the Begin and End dates as needed and then reference that view in your reports.

2) Extract lines 6 to 28 and insert it directly into a report as a command object. You can replace the Begin and End dates with command date range parameters if needed.

With either option, the Begin and End Dates could be replaced with calculations relative to today’s date, something like this:

WHERE
X.COL_DATE >= GetDate () - 366 AND
X.COL_DATE <= GetDate () + 366;

So if you find yourself needing a master “Dates” table, you can use this method to avoid maintaining a physical table.