did the grouped cfoutpout not work for you then ? On Sun, Sep 26, 2010 at 4:55 AM, GLM <g...@glmdesigns.com> wrote:
> > 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:337556 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
