>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.
If I understood you right:
declare @period_ID int
declare @tally table(n int)
set @period_ID = 3
insert into @tally select top (@period_id) row_number() over (ORDER BY t1.Object_ID) AS N
FROM Master.sys.All_Columns t1
CROSS JOIN Master.sys.All_Columns t2
select t3.seller_id, t3.Period_id,
(SELECT SUM(Amount)
FROM Commission
WHERE Seller_Id = t3.Seller_Id AND Period_id < t3.Period_id) AS Balance
from
(select distinct t2.seller_id, t1.N as Period_id
from @tally t1
cross join Commission t2) t3
where t3.Period_id = @Period_IDCetin
