By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,842 Members | 2,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,842 IT Pros & Developers. It's quick & easy.

Find remaining balance - not a simple SUM()

maxx233
P: 32
I need to figure out a way to generate a report to find customers in our DB that have unused credits on their account so we can void them out periodically - we manually assign them credits, they make purchases, but if they don't use the full balance of the credits in 24 hours we can negate the leftover (it's a promotional thing.)

There's two problems I'm wrestling with though - The table doesn't have a running balance field (not that big of deal), and sales (negative credits) are counted in the amounts field as a positive number, only signifying that it's a sale with a Code of S instead of C (Credit.) The only negative numbers in the Amount field are manually added credits that we've put in to balance someone's account in the past - we find someone with 10 credits still leftover, so we manually credit them -10.00 in order to bring them to a 0.00 balance.

Here's a small relevant example of what the DB looks like:

TransID.......CustID....TransDate................. .......Code....Amount
1................100.........2009-01-26 13:00:00.000...C.........100.00
2................200.........2009-01-26 14:00:00.000...C.........100.00
3................100.........2009-01-27 11:00:00.000...S.........50.00
4................200.........2009-01-27 16:00:00.000...S.........100.00
5................100.........2009-02-15 10:00:00.000...S.........25.00
6................100.........2009-02-17 09:00:00.000...C.........-25.00

If today is Feb 16, any ideas how I might generate a report to show all the customers with an outstanding balance... In this case showing customer 100 with a balance of 25.00 still remaining, and customer 200 with a 0.00 balance? CustID 100 would have a 25.00 balance still because they were credited 100.00, then had a Sale of 50.00 in TransID 3, then 25.00 in TransID 5. I added TransID 6 just to give a more complete picture of how the DB looks after we've balanced someone's account, in this case on the next day, Feb 17. We cannot alter the table in any way, structurally or as far as the data is concerned.

Any ideas would be greatly appreciated, I feel like my mind's gotten itself into a rut and there's probably something easy/creative I could do that I'm not seeing now!
Feb 27 '09 #1
Share this Question
Share on Google+
1 Reply


ck9663
Expert 2.5K+
P: 2,878
Questions....

1. 24 hours to the minute or you're willing to give your customer the whole day? Meaning If the card was first credited with 100 on feb 16, it will be zero-out on the 18th? Or you're concern with up-to-the minute zero-out of this card?

2. On your sample, CustID 100 was first credited on 01/26 but you only zero out the balance on 02/16. Should it be zero out hours a day after the first credit or the last use of the card?

-- CK
Feb 28 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.