Hey guys, thanks for your help:) Creating the logic in the WHERE section solved the problem. Again, thanks a lot:)
----- Original Message ----- From: "Bryan Love" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, February 13, 2002 1:50 PM Subject: RE: How do u add colums in SQL query and do a WHERE on the new co lumn > I assume that past_due_30 must have a value if past_due_60 has a > value... > > SELECT first_name, email_address, customer_number, total_due, > (past_due_30 + past_due_60 + past_due_90 + past_due_120) AS > totaloutstanding > FROM accountsreceivable > WHERE past_due_30 > 0 > or if pst_due_30 is nullable... > WHERE past_due_30 IS NOT NULL > or if past_due_30 doesn't have to have have a value for past_due_60 to > have > a value... > WHERE past_due_30 + past_due_60 + past_due_90 + past_due_120 > 0 > > +-----------------------------------------------+ > Bryan Love > Macromedia Certified Professional > Internet Application Developer > Database Analyst > Telecommunication Systems > [EMAIL PROTECTED] > +-----------------------------------------------+ > > "...'If there must be trouble, let it be in my day, that my child may > have > peace'..." > - Thomas Paine, The American Crisis > > > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, February 13, 2002 1:43 PM > To: CF-Talk > Subject: Re: How do u add colums in SQL query and do a WHERE on the new > column > > > Was wondering if anyone knows how to add up 4 columns in a query, give > the > result a new column name, and then use that new column name in the > WHERE > part of the query. > > Situation is where some customers are 30, 60, 90 or 120 past due. So > I'm > trying to add up the 30,60,90,120 column totals and save the result as > a new > column called TOTALOUTSTANDING > > Then we want to send emails to anyone where TOTALOUTSTANDING > 5 > (where > the total outstanding is more than $5) > > I tried this script, but the dang thing never worked:( > > <CFQUERY NAME="pastduenotice" DATASOURCE="customers"> > SELECT first_name, email_address, customer_number, total_due, > (past_due_30) > + (past_due_60) + (past_due_90) + (past_due_120) AS totaloutstanding > FROM accountsreceivable > where totaloutstanding > 0 > </CFQUERY> > > Appreciate any enlightenment on this one:) > > ______________________________________________________________________ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
