SQLTeam.com | Weblogs | Forums

Cumulate sales per week


#1

Hello,

I have the following query which will show the sales per week per representative.
But now I get the sales which is made in each week. But I want to cumulate this per week.
Example: Representative 1 has 100,- sales in week 1 and 200,- in week 2.
So I want to show 300,- in week 2 and so on (instead of 200,- in week 2).
Is this possible ?
Thanks !!

select
datepart(WEEK, invoicedate)as week,
SUM(Amount-VAT) as sales,
representative_id
from invoices
left outer join customer on invoices.Debnr = customer.cust_id
where Datepart(year, invoicedate) = '2016'
group by
datepart(WEEK,invoicedate),
representative_id
order by week


#2

Check out Window Functions. Specifically Aggregate Functions

Maybe something like this:

WITH sales AS
(
SELECT
datepart(WEEK, invoicedate) AS week,
SUM(Amount-VAT) AS sales,
representative_id
FROM
invoices
left outer join customer on invoices.Debnr = customer.cust_id
WHERE
Datepart(year, invoicedate) = '2016'
GROUP BY
datepart(WEEK,invoicedate),
representative_id
ORDER BY week
)
SELECT
week,
sales,
SUM(sales) OVER(PARTITION BY representative_id) AS total_sales
representative_id
FROM sales


#3

Thanks for your reply !

But when I use this query I get the cumulated 'total_sales' per representative over the whole year

week:1
Sales:100
total_sales: 500 (must be 100)

week: 2
Sales: 200
total sales: 500 (must be 300)

So every week the cumulated total sales is the same (sales over the whole year).
But I want the sales cumulated per week.
Thank you again.


#4

It's hard for me to give advice without being able to test the query for myself. Try adding additional fields into the OVER(PARTITION BY ) part. Like OVER(PARTITION BY rep_id, weekNbr). If that doesn't give you what you are looking for, then see if the article I posted a link for could help you out.


#5

This seams to a problem of RUNNING TOTAL. This should work fine for you.

WITH sales AS
(
SELECT
datepart(WEEK, invoicedate) AS week,
SUM(Amount-VAT) AS sales,
representative_id
FROM
invoices
left outer join customer on invoices.Debnr = customer.cust_id
WHERE
Datepart(year, invoicedate) = '2016'
GROUP BY
datepart(WEEK,invoicedate),
representative_id
ORDER BY week
)

SELECT a.[Week], a.representative_id , a.Sales, (SELECT SUM(b.Sales)
FROM sales b
WHERE b.[Week] <= a.[week])
FROM sales a
ORDER BY a.[Week];


#6

Hello EarlySunrise,

Thanks for your reply, I'm getting a step further but the problem now is that I get the same 'cumulative sum' for all representatives.

Every representative has now the same total sales amount of that week.
So it seems that the query does cumulate the sales per week the right way, but now it only need to split it into the right representative.

Thanks again.


#7

Hi keesvo,

I hope this should work for you.

SELECT a.representative_id , a.[Week], a.Sales, (SELECT SUM(b.Sales)
FROM sales b
WHERE b.[Week] <= a.[week] and a.representative_id = b.representative_id )
FROM sales a
ORDER BY a.representative_id, a.[Week];


#8

Hello Early Sunrise,

That does the trick !!
You're awesome, thank you very much.


#9

:slight_smile:


#10

I have one more question...
The query is perfect but some representatives have no sales in some weeks.
So now i get the following result:

REP_A - week1 - 2000,- (sales/week) - 2000,- (cumulate sales)
REP_A - week2 - 1000,- (sales/week) - 3000,- (cumulate sales)
REP_A - week4 - 1500,- (sales/week) - 4500,- (cumulate sales)

As you can see REP_A has no sales in week3.
Is there a way to show week3 with 0 sales/week and 3000,- cumulate ?
And then not only for week3 but all weeks where a rep has no sales that week.

Thank you again.