I should probably just sleep on this problem, but it's been bugging me. This seems like it should be easy enough to do, but everytime I look at the problem I change my mind about how to do it. I need to generate a A/R report for a given date range.

eg.

Range:8/1/1998 to 8/31/1998
Beginning Balance - Invoice Amt. - Ending Balance


I've got an Invoice table that includes:
Invoice#
TotalAmt (should really be calculated, but I didn't write this app)
SaleDate

I've also got a JobCost table that holds all the deposits made:
Invoice#
Amount
Date

The ending balance is all the deposits made up until the "end" date subtracted from the invoice amount.

The invoice amount is = to the invoice amount, unless the invoice wasn't "closed" before the "end" date of the report - then it's 0 (ie. the customer prepaid some amount against an unknown Invoice total).

The beginning balance is the tough one (or at least the one confusing me). It has the total of all the deposits made against the job before the start date subtracted from the invoice amount (of only invoices "closed" before the start date).

Somehow all of this combines together to generate the report. Any ideas/suggestions on what the SQL code might look like? I keep coming up with a ton of different cursors that somehow need to be joined together. I can't help but think I should be able to handle this in 1-3 cursors instead of the mess I'm coming up with (and not so sure will even work).