One pattern that I found very useful (I think the first time I saw it was on SQL Server Central), is a Numbers Table.
So what is a numbers table? It's a table pre-loaded with numbers :)
Literally 1, 2, 3, 4, 5, ..., 100.000 (the upper limit depends on the maximum estimated size of the results).
Ok. So how can it help you? How can it be used? It has many uses, beign the most common ones reporting sheets.
Suppose you have to make a report showing all the orders by day of month (or year). But with a business twist (a requirement), you must show all dates, even if there's not an order for that day. Assuming NorthWind orders table:
You want a report that shows the number of orders for every day:
So a simple group by date in the orders date doesn't cut it (we have no orders for some dates).
Enter the Numbers table. In this case, what we want is a table with the dates we want to report (the month, the year) and left join it with the # of orders by day. But we can generalize it, and instead of a table with dates, we can use a Numbers table. Something like:
declare @date datetime
set @date = '1996-07-31'
dateadd(day, N.number, @date) as Date,
left outer join (
select OrderDate, COUNT(*) as NumOrders
group by OrderDate
) O on dateadd(day, N.number, @date) =
where N.number <= 31
order by Date
And voilá, the report we want comes out. Note also, that with this approach, it's easy to change the granularity of the report (by hour, by month, by year). The number column (of Numbers table) is added as days to the initial date of the report, which acomplishes us a table with 1 month dates (31 days).
If you want to see some other examples (some esoteric), see also: