Announcement

As of April 17, 2018, this site is now in read-only mode. To start new Blackbaud Developer’s discussions, you will find Developer Discussions in the Blackbaud CRM Community.

To post or respond to Blackbaud Community discussions, you will have to login using a www.blackbaud.com login. Learn how to login here. If you have questions or need assistance, please email community@blackbaud.com.

The "Numbers" table

By: TripOtt

Posted on: September 5, 2012

I recently overheard the question at Blackbaud the other day, “What is the “Numbers” table used for?.  We’ll let me try to relay an answer to you good people.  The numbers table can be a pretty powerful thing to use.  Often times in SQL, you find yourself needing to generate numbers or finding gaps in numbered sequences.  You can use it to order things sequentially when querying for data.  For example, you can use them to help generate date/time in your queries:

DECLARE @STARTDATE as DATETIME = ‘1/1/2012′
DECLARE @ENDDATE as DATETIME  = ’12/31/2012’

select DATEADD(d,NUM,@STARTDATE) from NUMBERS where DATEADD(d,NUM,@STARTDATE) <= @ENDDATE

For more information on using a Numbers table see:

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/you-require-a-numbers-table.aspx

http://www.simple-talk.com/sql/database-administration/creative-solutions-by-using-a-number-table/

Leave a Reply

Privacy Policy | Sitemap | © 2011 Blackbaud, Inc. All Rights Reserved

Digital Ocean