Sql-server – Cannot SUM the value of a sales amount field – various criteria, sales rep id and date ranges

group bysql serversql-server-2016sum

I wonder if someone can help me with this SQL Server 2016 query.

I have two tables with following data

TblSalesRep

This is the table holding the history of the territories that the sales rep looked after and the date range that they looked after it.

TerritoryId SalesRep FromDate ToDate
15 Anne 2020-01-01 2020-06-02
15 Dave 2020-06-03 2020-06-16
15 Anne 2020-06-17 2020-06-22
18 Anne 2020-06-23 2999-12-31

tblSales

This is the table that holds the sales made by territory

TerritoryId TransactionId TransactionDate ProductCode Amount
15 1 2020-04-15 Bags 50
15 1 2020-04-15 Bags 50
15 2 2020-06-02 Bag 25
15 3 2020-06-03 Shoes 60
15 4 2020-06-07 Shoes 10
15 5 2020-06-17 Bags 15
15 6 2020-06-18 Bags 25
15 7 2020-06-25 Coat 100
18 8 2020-06-27 Bags 20

I’m trying to produce a report that displays the SalesRep and the total sold between two dates for a given SalesRep.

For example, if I provided a SalesRep = ‘Anne’ and a date range of ‘1 Jun 2020’ to ‘30 Jun 2020’ I would expect a result back of :

SalesRep    Sum
Anne        185

This is because it should exclude any transactions made between 03 Jun 2020 and 16 Jun 2020 as the territory was looked after by Dave for a couple of weeks and he conducted that business between those dates.

I’ve tried using the SUM() function whilst joining to TblSalesRep but the figures are not what I’m expecting.

Best Answer

There are more compact ways to write this, but this illustrates a way to do it.

First select the territories and periods that Anne was looking after that territory.

Then pull all the sales where the transaction date falls in one of the periods when Anne was covering the territory where the sale was made.

Then sum up the sales.

WITH
 territory_periods AS (
SELECT SalesRep, TerritoryID, FromDate, ToDate
  FROM tblSalesRep 
 WHERE SalesRep = 'Anne'
)
,sales AS (
SELECT p.SalesRep, s.Amount
  FROM tblSales s
  JOIN territory_periods p
    ON s.TerritoryID = p.TerritoryID 
   AND s.TransactionDate BETWEEN p.FromDate AND p.ToDate
)
SELECT SalesRep, Sum(Amount)
  FROM sales 
GROUP
    BY SalesRep
;
Related Question