How return rows with no value

1

Hello everyone,

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 (

_select _
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())
GROUP BY
_datepart(WEEK,invoices.date), _
customer.rep__ref

)
_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];

Thank you

2

You could use a TALLY TABLE (physical or dynamically generated via, for example, a Function) for the weeks between your start/end point, and then OUTER JOIN your [sales] table (i.e. so you get NULL rows for any missing weeks).

It would help if you format code you post here (the [<>] button should do the trick), otherwise the MarkDown will garble your code :frowning: