Each period (basically monthly) I need to generate a commission report for every seller. I have a commission table and to get the data I do something similar to this:
SELECT a.Period_id, a.SellerId,
(SELECT SUM(Amount) FROM Commission WHERE SellerId = a.SellerId AND Period_id < a.Period_id) AS Balance
FROM Commission a
With this query, I getall the transactions and an extra field with a balance of previous periods, this is done with the "SELECT SUM(amount)...", this is repeated for every transaction record but no biggie, it's there, I get the data and generate the report no problem.
This is the problem; lets say we are printing period MAR 2009 and a seller has no transactions for this period, then 2 things can happen:
1) There's no balance for periods before MAR 2009, so the report doesn't print for the seller (no problem)
2) There's a balance for periods before MAR 2009, so the report has to print for the seller (problem)
so in situation #2 since there are no records for MAR 2009 then it doesn't get to SUM to get the beginning balance. I need to somehow include in my cursor the ones that have no transactions but a previous balance so it can print in my report (basically will show the balance and the rest will be 0 since there are no transactions). I have an idea of how to do this with 2 queries and some processing in foxpro but maybe there's a better way to do it in one query.
Any ideas?
Thanks.