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
SQL Query to find Negative Remaining Balances on Orders
SQL Query to find Negative Remaining Balances on Orders
Able Customer Since 1999 Currently Running on GOLD R12 SR1 and PCI Certified.
Re: SQL Query to find Negative Remaining Balances on Orders
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
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.
- 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
Calvis, here is a basic query, but you probably want something more complicated?
select ordernumber from ac_orders where TotalCharges-TotalPayments <0
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
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
Re: SQL Query to find Negative Remaining Balances on Orders
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.
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.
- 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
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.
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
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
Re: SQL Query to find Negative Remaining Balances on Orders
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.![Smile :)](./images/smilies/icon_smile.gif)
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.
![Smile :)](./images/smilies/icon_smile.gif)
Able Customer Since 1999 Currently Running on GOLD R12 SR1 and PCI Certified.