SQL Query to find Negative Remaining Balances on Orders

For general questions and discussions specific to the AbleCommerce 7.0 Asp.Net product.
Post Reply
User avatar
calvis
Rear Admiral (RADM)
Rear Admiral (RADM)
Posts: 710
Joined: Tue Jan 27, 2004 3:57 pm
Location: Redmond, WA

SQL Query to find Negative Remaining Balances on Orders

Post by calvis » Sun Mar 27, 2016 8:29 pm

Hi There,

Can anyone kindly help me figure out some SQL code to locate negative remaining balances in orders? We normally have negative balances when customers are due refunds because they either remove items from their order or downgraded their shipping. I need some SQL Code to help me locate the negative remaining balances so we can give them refunds.

I played around with it for few hours but ran out of ideas.

Thanks


negative_balance.jpg
Able Customer Since 1999 Currently Running on GOLD R12 SR1 and PCI Certified.

User avatar
calvis
Rear Admiral (RADM)
Rear Admiral (RADM)
Posts: 710
Joined: Tue Jan 27, 2004 3:57 pm
Location: Redmond, WA

Re: SQL Query to find Negative Remaining Balances on Orders

Post by calvis » Mon Mar 28, 2016 12:01 pm

50 bucks via Paypal for someone that can help me figure this out.

Must be SQL Code.

Fields needed:

Order Number, Order Date, and Negative Balance AMT
Able Customer Since 1999 Currently Running on GOLD R12 SR1 and PCI Certified.

User avatar
jmestep
AbleCommerce Angel
Posts: 8164
Joined: Sun Feb 29, 2004 8:04 pm
Location: Dayton, OH
Contact:

Re: SQL Query to find Negative Remaining Balances on Orders

Post by jmestep » Tue Mar 29, 2016 12:00 am

Calvis, here is a basic query, but you probably want something more complicated?
select ordernumber from ac_orders where TotalCharges-TotalPayments <0
Judy Estep
Web Developer
jestep@web2market.com
http://www.web2market.com
708-653-3100 x209
New search report plugin for business intelligence:
http://www.web2market.com/Search-Report ... -P154.aspx

User avatar
calvis
Rear Admiral (RADM)
Rear Admiral (RADM)
Posts: 710
Joined: Tue Jan 27, 2004 3:57 pm
Location: Redmond, WA

Re: SQL Query to find Negative Remaining Balances on Orders

Post by calvis » Tue Mar 29, 2016 12:15 am

Prefect, that got me going in the right direction. I was able to find the orders that I need to give a refund to.

Send me your Paypal address and I will shoot you a gratitude of my appreciation.
Able Customer Since 1999 Currently Running on GOLD R12 SR1 and PCI Certified.

User avatar
jmestep
AbleCommerce Angel
Posts: 8164
Joined: Sun Feb 29, 2004 8:04 pm
Location: Dayton, OH
Contact:

Re: SQL Query to find Negative Remaining Balances on Orders

Post by jmestep » Wed Mar 30, 2016 12:31 am

calvis,
I can't take a payment for that, but I do appreciate the offer. I was thinking it was going to be complicated also until I remembered that the order table had a total payments field.
Judy Estep
Web Developer
jestep@web2market.com
http://www.web2market.com
708-653-3100 x209
New search report plugin for business intelligence:
http://www.web2market.com/Search-Report ... -P154.aspx

User avatar
calvis
Rear Admiral (RADM)
Rear Admiral (RADM)
Posts: 710
Joined: Tue Jan 27, 2004 3:57 pm
Location: Redmond, WA

Re: SQL Query to find Negative Remaining Balances on Orders

Post by calvis » Wed Mar 30, 2016 7:01 am

You are right, I never knew there was a TotalCharges and TotalPayments field.

I thought I was going to have to calculate those fields during the query so I could make it work. Thanks to you a few customers got their refunds that were due. :)
Able Customer Since 1999 Currently Running on GOLD R12 SR1 and PCI Certified.

Post Reply