Re: How do u add colums in SQL query and do a WHERE on the new co lumn
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:( > > > 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 > > > 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
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:( 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 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
RE: How do u add colums in SQL query and do a WHERE on the new co lumn
what is the datatype of the columns "past_due_120", "past_due_90"etc. Make sure they are number. -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:( 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 Appreciate any enlightenment on this one:) __ 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=coldfusiona 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
