Friday, November 09, 2007

SQL Tricks/Patterns 1 - Numbers Table

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'

            dateadd(day, N.number, @date) as Date, 
            isnull(O.NumOrders, 0)
           Numbers N
          left outer join (
                 select OrderDate, COUNT(*) as NumOrders
                 from Orders
                 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:

The Joy of Numbers

The Helper Table Workbench


Technorati tags: , ,

No comments: