I have a query which returns the amount of sales per week per representative.
But the only thing is that there are representatives which don't have sales every week.
So i get now the following results (as e.g.).
Rep 1, week 1, sales 1000, cumulative sales 1000
Rep 1, week 2, sales 2000, cumulative sales 3000
Rep 1, week 4, sales 1000, cumulative sales 4000
As you can see week 3 is missing.
So I want to query it that week 3 is also returned with the cumulative sales of the week therefore and that for all weeks there are no sales for the representative.
Rep1, week 3, sales 0, cumulative sales 3000
The query I use is the following:
WITH Sales AS (
customer.rep__ref as representative,
DATEPART(WEEK, invoices.date) AS Week,
sum(amount) as Sales,
from invoices left outer join customer
on invoices.customer_id = customer.customer_id
where YEAR(date) = YEAR(GETDATE())
_SELECT a.representative , a.[Week], a.Sales, (SELECT SUM(b.Sales) _
FROM sales b
WHERE b.[Week] <= a.[week] and a.representative = b.representative )
FROM sales a
ORDER BY a.representative, a.[Week];