How about dynamically generating a set of UNIONed queries (one for each of the years in the range)?
-- WSS4CF - WS-Security framework for CF http://wss4cf.riaforge.org/ On 27 September 2010 07:58, Mike Chabot <[email protected]> wrote: > > If someone is in office with a range of 2004 - 2006, the year 2005 > does not appear in the SQL results, yet 2005 needs to be grouped on. > You can't group on a value that doesn't exist. There is no year column > in the output to group on, only a start date and an end date. > > -Mike Chabot > > On Sun, Sep 26, 2010 at 4:02 PM, Russ Michaels <[email protected]> wrote: >> >> Mike, >> >> why do you would you need to know the exact years to use grouped output? >> This is not how it works. >> Grouped output simply groups the output by the defined column >> So if you group ny year, it will output all records with the same year >> together allowing you to then separate the output, add headings etc. >> >> >> On Sun, Sep 26, 2010 at 4:34 PM, Mike Chabot <[email protected]> wrote: >> >>> >>> The grouped output doesn't work because the exact years are not known >>> in the query output. Only the date range is known. >>> >>> -Mike Chabot >>> >>> On Sun, Sep 26, 2010 at 5:20 AM, Russ Michaels <[email protected]> >>> wrote: >>> > >>> > did the grouped cfoutpout not work for you then ? >>> > >>> > On Sun, Sep 26, 2010 at 4:55 AM, GLM <[email protected]> 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:[email protected]] >>> >> 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 <[email protected]> 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:[email protected]] >>> >> > 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 <[email protected]> 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:337568 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
