Summing Dollar Amounts from Query- Evaluate? Loop? CFSET? Let SQL DO IT?

[seasonedweb]

SQL Server 2008 R2

CF 9.0.1

Data type: money, null

Query name: get_payments

Fields:

payment_subtotal

payment_add

payment_subtract

Greetings

The best practice method to evaluate, add/subtract dollar sums to arrive at a grandTotal (that works) is tough to find.

I simply want to display the grand total on a list of orders in a "grandTotal" column.

Some advise doing the math in the DB,

select sum(payment_subtotal+payment_add-payment_subtract) as grandTotal

but this does not work because the query selects from 4 tables using left-Joins- where in the query would this appear?

others use

<cfset grandTotal = 0>  <cfset grandTotal =  grandTotal + (get_payments.payment_subtotal + get_payments.payment_add) etc.

or

<cfset grandTotal = ArraySum(get_payments['payment_subtotal'])

+ ArraySum(get_payments['payment_add']) etc.

or Loops:

<cfset grandTotal = 0>

<cfloop query="get_payments"><cfset grandTotal =  grandTotal + ( #get_payments.payment_amount# + #get_payments.handling_cost# + #get_payments.Shipping_cost# ) />

single quotes, double quotes, with and without ## are not clear- and none of the above methods have been successful.

since the field could be null, I tried:

<cfoutput>

<cfif #get_payments.payment_subtotal# IS "">

<cfset get_payments.payment_subtotal=0>

<cfelseif  #get_payments.payment_add# IS "">

<cfset get_payments.payment_add=0>

<cfelseif  #get_payments.payment_subtract# IS "">

<cfset get_payments.payment_subtract=0>

</cfif>

</cfoutput>

The query results are structured:

<cfoutput query="get_payments">

<tr bgcolor="###iif(currentrow MOD 2,DE('ffffff'),DE('d9ecff'))#">

<td width="7%" class="tddynamic">#order_number#</td>

<td width="5%" class="tddynamic">#order_ID#</td>

<td width="7%" class="tddynamic">#DateFormat(payment_date, "mm/dd/yyyy")#</td>

<td width="7%" class="tddynamic">#payment_ID#</td>

<td width="15%" class="tddynamic">#client_company#</td>

<td width="20%" class="tddynamic">#order_property_street#, #order_property_city#</td>

<td width="15%" class="tddynamic">#appraiser_lname#</td>

<td width="7%" class="tddynamic">#DollarFormat(grandTotal)#</td>

<td width="5%" class="tddynamic">#payment_checknum#</td>

</tr>

</cfoutput>

Any help with the "foolproof" 2015 best practice would be appreciated.

Thank you in advance.

[BKBK]

seasonedweb wrote:

<cfoutput>

<cfif #get_payments.payment_subtotal# IS "">

<cfset get_payments.payment_subtotal=0>

<cfelseif  #get_payments.payment_add# IS "">

<cfset get_payments.payment_add=0>

<cfelseif  #get_payments.payment_subtract# IS "">

<cfset get_payments.payment_subtract=0>

</cfif>

</cfoutput>

You could improve on this logic. It picks just one of the conditions, whereas it has to consider them all, separately.

Correct is:

<cfoutput query="get_payments">

<cfif payment_subtotal IS "">

    <cfset payment_subtotal=0>

</cfif>

<cfif payment_add IS "">

    <cfset payment_add=0>

</cfif>

<cfif payment_subtract IS "">

    <cfset payment_subtract=0>

</cfif>

<tr bgcolor="###iif(currentrow MOD 2,DE('ffffff'),DE('d9ecff'))#">

etc.

</tr>

</cfoutput>

[ShawnPO]

I would still let the database do it. That's what it's for. If you have access to the database. This looks perfect for a stored procedure.

Do those payment amounts come from different tables? If they do, use a COALESCE(paymentX,0) for the specific field, and it will properly do the math. With a LEFT JOIN, you can get a NULL value, and you can't do math on a NULL. So your result becomes NULL.

Because you're using SQL 2008, you can also do a Common Table Expression and do a simple join from there.

Unfortunately, without more context of the data structure, I can't give you a better answer.

But looking at your cfoutput, I'll make some assumptions to build up a query.

Order_ fields are Order table.

Payment_ fields are Payment table. Payment is linked to Order and Client.    paymentAmount+shippingAmount == grandTotal  <<< my assumption here is that this is the grandTotal for the Payment. If it's supposed to be a Grand Total for the entire Order or for the Client, you'll have to get a bit more involved and GROUP these together, which will completely change your JOINs.

Client_ is Client table. Client is linked to Order and Payment.

Appraiser_ is Appraiser table. Appraiser is linked to Order.

One word of caution, when using OUTER JOINs, be very careful how you join them together. You may not be getting the results you think you're getting. Especially when you start adding functions into the mix. Keep all of the criteria for the JOIN inside the JOIN rather than part of it in the WHERE clause. See my example below for the Client table.

My example:

SELECT o.number as Order_Number, o.ID as Order_ID, o.Property_Street as Order_Property_Street, o.Property_City as Order_Property_City

     , p.ID as Payment_ID, p.date as Payment_Date, p.checknum as Payment_CheckNum

          , (COALESCE(p.paymentAmount,0) + COALESCE(p.ShippingAmount,0)) as Payment_GrandTotal

     , c.company as Client_Company

     , a.lname as Appraiser_LName

FROM Order o

LEFT OUTER JOIN Payment p ON p.OrderID = o.ID

LEFT OUTER JOIN Client ON c.OrderID = o.ID

     AND c.PaymentID = p.ID

LEFT OUTER JOIN Appraiser a ON a.OrderID = o.ID

EDIT:

And I just noticed that this question was from January, so I'm guessing you've likely already got a solution. However, if anyone else has a similar problem, I would still suggest dealing with it in the database. And again, without having more knowledge of the database schema and how those things relate to each other, or your business rules, I can't do much better with that query. And it could very well change it quite a bit. If the GrandTotal isn't for the Payment, is it for the entire Order or for the entire Client?

[formosa_carda10060181]

the sum and average of to do list google drive sync/ to-do list spread sheet