I have found it very useful to have date tables to perform date range queries. For example if you want to show total sales per month over the last 12 months. You can base your range off of the months over the last 12 months from your month date table. Doing a left join will allow you to return a record for each month even if there are no invoices for that month.

Date tables also give you an easy way of determining related values such as the next month begin date without having to calculate them on the fly. When you are dealing with many records, those dynamic calculations can have a definite performance impact.

The following SQL script creates a date table for the days, months and years between the start date and end date specified at the beginning of the script.

BuildDateTables.zip (.7 KB)

So in the previous example we could compile the sales summary as follows:

SELECT M.[Month], ISNULL(SUM(INV.Amount),0) AS TotalSales
FROM Months AS M LEFT OUTER JOIN Invoice AS INV ON INV.Date BETWEEN M.[Month] AND M.NextMonthBegin
WHERE M.[Month] BETWEEN DATEADD(month, -12, GETDATE()) AND GETDATE()
GROUP BY M.[Month]
ORDER BY M.[Month] DESC