Mike thanks for the lead. It had crossed my mind to build a numbers table but I didn't think it would work. (I've never used them myself.)
I thought that it was somewhat excessive as there would have to be at least 365x220 rows (over 80,000). In the examples I gave I think one row per year (220) would be more than good enough but there are numerous exceptions (people dying in office, getting nominated to higher office, etc...) I'll give it a shot. Thx -----Original Message----- From: Mike Chabot [mailto:mcha...@gmail.com] Sent: Saturday, September 25, 2010 10:50 PM To: cf-talk Subject: Re: cfoutput or cfloop? which is the more practical solution You can do this in SQL, although the methods I can think of involve advanced techniques so I hesitate to recommend them based on your stated SQL experience. One technique involves joining to a "numbers table" which will transform the start and end dates into one row per year. You can use the cfloop technique in your first post. It should get the job done. It isn't the fastest method, but it could be fast enough. One alternative to consider would be to have one big database query, then loop over the one query once and build up an array or a structure containing the data you want to output, then cfoutput the array or structure. -Mike Chabot On Fri, Sep 24, 2010 at 10:28 PM, GLM <g...@glmdesigns.com> wrote: > > Thanks Mike, > > I'm not a SQL expert but I don't see how. I can do something with particular > fields such as the year the person was elected (governorDateStart) or when > the person left office governorDateEnd > > > SELECT > governorParty, > COUNT(governorState), > governorDateStart > WHERE gender=female > > FROM > governors > > GROUP BY governorParty, governorDateStart > > And get something along the line of: > > 2001 - Republican - 2 > 2002 - Republican - 1 > 2003 - Democratic - 3 > 2003 - Republican - 1 > 2004 - Democratic - 1 > 2004 - Republican - 1 > 2005 - Democratic - 1 > 2006 - Republican - 1 > 2009 - Democratic - 1 > 2009 - Republican - 1 > > But what's desired is: how many are in office at a particular time. > For instance: > list the governors in 1889; > list the number of female governors in 2005 > > thx > > > > > -----Original Message----- > From: Mike Chabot [mailto:mcha...@gmail.com] > Sent: Friday, September 24, 2010 7:35 PM > To: cf-talk > Subject: Re: cfoutput or cfloop? which is the more practical solution > > > Are you able to craft a query that will return the results you need > without ColdFusion having to do any extra parsing of it? That is the > first thing I would try. In your brief example it seems like that > would be solved using a GROUP BY statement in the query. > > -Mike Chabot > > On Fri, Sep 24, 2010 at 7:23 PM, GLM <g...@glmdesigns.com> wrote: >> >> I have a database with presidents, governors, etc. and need to be able to >> pull out information such as: >> >> >> >> Get the number of all female governors over the years and spit out > something >> on the order of: >> >> >> >> 1789 : 0 >> >> 1790 : 0 >> >> . >> >> 2005 : 10 >> >> . >> >> 2010 : 6 >> >> >> >> The database has dateStarted, dateEnded >> >> >> >> I can loop through this [SIMPLIFIED CODE] >> >> >> >> <cfloop> >> >> <cfquery> >> >> SELECT >> >> FROM >> >> WHERE year == #desiredYear# >> >> >> >> </cfquery> >> >> </cfloop> >> >> >> >> This seems like a foolish way to do this. Is there a better way to do it? > Is >> it better to make one query and then use CF to parse it >> >> >> >> >> >> >> >> >> >> >> >> > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:337555 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
