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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
