>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.

Roy,

Use derived tables instead - they perform much better.

select a.Period_ID, a.SellerID from Commission A 
RIGHT JOIN (select SUM(amount) as PreviousAmount, SellerID from Commission 
where Period_ID <@Period_ID) X on a.SellerID = X.SellerID and A.Period_ID = @period_ID