If you're using MSSQL, you can use my generateDateTable for creating a dynamic date table which you can use:
http://blog.pengoworks.com/index.cfm/2009/1/8/Dynamically-generating-a-table-of-dates-in-Microsoft-SQL-Part-Deux While a numbers table is more efficient if you're dealing with 1000s of rows, this works really well for most use cases. -Dan On Sun, Sep 26, 2010 at 10:46 PM, GLM <g...@glmdesigns.com> wrote: > > Thx Mike, > > I've started my reading early. One of my questions was: how much space > would > the numbers table occupy? > > Found that that 2G rows (2,000,000,000) is roughly 8GB in size. > > http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=NumbersTable > > I'll need at the most 365 x 3000 years if the clients wanted one database > that went from Egyptian pharaohs to American Presidents (yeah right) that > would be roughly 1,000,000 rows. This expanded table would be roughly 4MBs > (1/2000 of 8GBs) so I don't particularly have to worry about size. > > Since a numbers table for the US is "only" about 80,000 rows the numbers > table would be less than 400K. Not bad at all. > > - Gil > > -----Original Message----- > From: Mike Chabot [mailto:mcha...@gmail.com] > Sent: Sunday, September 26, 2010 8:08 PM > To: cf-talk > Subject: Re: cfoutput or cfloop? which is the more practical solution > > > I was thinking years, but you could do days as well. It depends on > what you want to group on in the output. > > To save you some searching, below is SQL to fill a numbers table for > SQL Server, if you go that route. As I said earlier, the other methods > work fine also. I tend to prefer doing as much as possible on the > database server, since databases are optimized for handling data. > > CREATE TABLE [dbo].[numbers]( > [number] [int] NOT NULL, > CONSTRAINT [PK_numbers] PRIMARY KEY CLUSTERED > ( > [number] ASC > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY > = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = > 100) ON [PRIMARY] > ) ON [PRIMARY] > > > declare @counter int > set @counter = 0 > begin tran > while @counter < 8000 > begin > set @counter = @counter + 1 > INSERT INTO numbers (number) > VALUES (@counter) > print 'The counter is ' + cast(@counter as char) > end > commit > > -Mike Chabot > > http://www.linkedin.com/in/chabot/ > > On Sun, Sep 26, 2010 at 2:50 PM, GLM <g...@glmdesigns.com> wrote: > > > > Maybe I don't have a clear concept of a numbers table (I'll be reading up > on > > them tomorrow.) but wouldn't I need a "number" to correspond to the units > in > > question (in this case days.) > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337584 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
