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.......C ustID....TransD ate............ ............Cod e....Amount
1.............. ..100.........2 009-01-26 13:00:00.000... C.........100.0 0
2.............. ..200.........2 009-01-26 14:00:00.000... C.........100.0 0
3.............. ..100.........2 009-01-27 11:00:00.000... S.........50.00
4.............. ..200.........2 009-01-27 16:00:00.000... S.........100.0 0
5.............. ..100.........2 009-02-15 10:00:00.000... S.........25.00
6.............. ..100.........2 009-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!
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.......C ustID....TransD ate............ ............Cod e....Amount
1.............. ..100.........2 009-01-26 13:00:00.000... C.........100.0 0
2.............. ..200.........2 009-01-26 14:00:00.000... C.........100.0 0
3.............. ..100.........2 009-01-27 11:00:00.000... S.........50.00
4.............. ..200.........2 009-01-27 16:00:00.000... S.........100.0 0
5.............. ..100.........2 009-02-15 10:00:00.000... S.........25.00
6.............. ..100.........2 009-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!
Comment