Re: Problem with math in query !!!
Hi, I'm having a prob. with the following query to generate a report. There are 3 colums called: pastdue30 pastdue60 payments region So what I'm trying to do is add up pastdue30 and pastdue60 amounts and then minus payments - which is payment a customer has made to show what the net balance is thats still owed. The report needs to display only customers who are past due more than $1, hence the attempt to define this through the WHERE clause. CFQUERY NAME=accountsreceivable DATASOURCE=data SELECT region, payments - (pastdue30 + pastdue60) as netdue FROM accounts_receivable where (payments) - (pastdue30 + pastdue60) 1 order by total_due ASC /CFQUERY Any ideas where I might be messing up on this code? Also, if you wanted to summarize totals by region, and show the % each region is contributing to the overall total of past due accounts with the company, whats the best way to write a query for that? Thanks, Pardee. __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. 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: Problem with math in query !!!
What error do you get? This may not be the culprit but I'd wrap payments - (pastdue30 + pastdue60) in another set a parens. (payments - (pastdue30 + pastdue60)) just to make sure SQL (I presume) knows for certain what you are doing. Just some initial off the top of my head stuff J. John Wilker Web Applications Consultant, and Author Macromedia Certified ColdFusion Developer President/Founder, Inland Empire CFUG. www.red-omega.com more people are killed by donkeys than by airplane crashes each year -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 21, 2002 7:10 PM To: CF-Talk Subject: Re: Problem with math in query !!! Hi, I'm having a prob. with the following query to generate a report. There are 3 colums called: pastdue30 pastdue60 payments region So what I'm trying to do is add up pastdue30 and pastdue60 amounts and then minus payments - which is payment a customer has made to show what the net balance is thats still owed. The report needs to display only customers who are past due more than $1, hence the attempt to define this through the WHERE clause. CFQUERY NAME=accountsreceivable DATASOURCE=data SELECT region, payments - (pastdue30 + pastdue60) as netdue FROM accounts_receivable where (payments) - (pastdue30 + pastdue60) 1 order by total_due ASC /CFQUERY Any ideas where I might be messing up on this code? Also, if you wanted to summarize totals by region, and show the % each region is contributing to the overall total of past due accounts with the company, whats the best way to write a query for that? Thanks, Pardee. __ Get the mailserver that powers this list at http://www.coolfusion.com 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: Problem with math in query !!!
Thanks, that solved the problem:) - Original Message - From: John Wilker [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Thursday, March 21, 2002 6:37 PM Subject: RE: Problem with math in query !!! What error do you get? This may not be the culprit but I'd wrap payments - (pastdue30 + pastdue60) in another set a parens. (payments - (pastdue30 + pastdue60)) just to make sure SQL (I presume) knows for certain what you are doing. Just some initial off the top of my head stuff J. John Wilker Web Applications Consultant, and Author Macromedia Certified ColdFusion Developer President/Founder, Inland Empire CFUG. www.red-omega.com more people are killed by donkeys than by airplane crashes each year -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 21, 2002 7:10 PM To: CF-Talk Subject: Re: Problem with math in query !!! Hi, I'm having a prob. with the following query to generate a report. There are 3 colums called: pastdue30 pastdue60 payments region So what I'm trying to do is add up pastdue30 and pastdue60 amounts and then minus payments - which is payment a customer has made to show what the net balance is thats still owed. The report needs to display only customers who are past due more than $1, hence the attempt to define this through the WHERE clause. CFQUERY NAME=accountsreceivable DATASOURCE=data SELECT region, payments - (pastdue30 + pastdue60) as netdue FROM accounts_receivable where (payments) - (pastdue30 + pastdue60) 1 order by total_due ASC /CFQUERY Any ideas where I might be messing up on this code? Also, if you wanted to summarize totals by region, and show the % each region is contributing to the overall total of past due accounts with the company, whats the best way to write a query for that? Thanks, Pardee. __ Get the mailserver that powers this list at http://www.coolfusion.com 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
