One more note ... Do you have any control over table schemas? If you have to filter on that many columns, and every row has all those columns, then the table really needs to be broken into three, one with the profiles, one with the certifications, and one joining the two with the expiration date. That would make the data retrieval really easy.
That would really be your best bet. --- Ben -----Original Message----- From: Adam Bourg [mailto:adam.bo...@gmail.com] Sent: Monday, January 24, 2011 1:23 PM To: cf-talk Subject: Re: Coldfusion8: Multiple checks within a CFIF for a valid date >Sounds like you are retrieving data and then filtering on the client >with CF based date math. And if so, then the best advice I can give you is don't. >This is the type of thing you need to do at the database level (in a >SELECT statement or a view or a stored procedure etc.). DBMSs are >really good at this type of date calculation, and you could easily >create calculated fields with all of the expiration values calculated >automatically, and then you just SELECT the ones with expired dates. > >--- Ben Thanks Ben. I've been reading your Coldfusion 8 books, they're very good btw! How would I do that? Here's what my CFC looks like now: <!--- Get the dates for the report generator loop ---> <cffunction name="getExpirationDate" returntype="query"><br> <!--- The where checks to see if it is a recent date this ties into the generator.cfm page. It prevents the database from outputting data older the the 2008 espically if there is an emply row. Will do validation on the Generator.cfm to display and pass to the report to only show dates with a valid date. 1900 is the SQL server's default 0 value. Filtering this out will give us an accurate report both on the SQL end but also needed on the CFM end. ---> <cfquery datasource="#DSN#" name="getExpirationDate"> SELECT profileID, dateDiff(day,getDate(),cprAdultExp) as adultExp, dateDiff(day,getDate(),cprInfantChildExp) as kidExp, dateDiff(day,getDate(),cprFPRExp) as frpExp, dateDiff(day,getDate(),aedExp) as aExp, dateDiff(day,getDate(),firstAidExp) as aidExp, dateDiff(day,getDate(),emtExp) as eExp, dateDiff(day,getDate(),waterSafetyInstructionExp) as waterExpt, dateDiff(day,getDate(),bloodPathogensExp) as bloodExp, dateDiff(day,getDate(),oxygenAdminExp) as oxyExp, dateDiff(day,getDate(),lifegaurdingExp) as lifeExp, dateDiff(day,getDate(),wildernessResponderExp) as wildExp, certNotes FROM mod_studentCertifications WHERE cprAdultExp>'2008-01-01' or cprInfantChildExp>'2008-01-01' or cprFPRExp>'2008-01-01' or aedExp>'2008-01-01' or firstAidExp>'2008-01-01' or emtExp>'2008-01-01' or waterSafetyInstructionExp>'2008-01-01' or bloodPathogensExp>'2008-01-01' or oxygenAdminExp>'2008-01-01' or lifegaurdingExp>'2008-01-01' or wildernessResponderExp>'2008-01-01' </cfquery> <cfreturn getExpirationDate> </cffunction> <!--- // End get the expiration date ---> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341143 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
