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:
OrderId Date
10270 01/08/1996
10271 01/08/1996
10272 02/08/1996
10273 05/08/1996
10274 06/08/1996
...
You want a report that shows the number of orders for every day:
Date #Orders
01/08/1996 2
02/08/1996 1
03/08/1996 0
04/08/1996 0
05/08/1996 1
06/08/1996 1
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'
select
dateadd(day, N.number, @date) as Date,
isnull(O.NumOrders, 0)
from
Numbers N
left outer join (
select OrderDate, COUNT(*) as NumOrders
from Orders
group by OrderDate
) O on dateadd(day, N.number, @date) =
O.OrderDatewhere 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:
