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

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.

• Hi and welcome to the forum! Could you please provide a fiddle of your tables and data - help us to help you! Commented Dec 29, 2020 at 21:00
• Are you sample TerritoryIds correct? Commented Dec 29, 2020 at 21:12

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
;
``````
• Thanks for your speedy response Stephen. That T-SQL produces the figure of 185 for 'Anne' in June, however where would I provide the From/To Date range if I was looking for the figures over a different date, for example from 1 Jan 2020 to 31 Dec 2020? I'm not too sure if the query is working off the first date range in the SalesRep table.
– Dave
Commented Dec 29, 2020 at 21:09
• If you restrict the sales subquery by adding "AND s.TransactionDate BETWEEN '2020-01-01' AND '2020-12-31'", or whatever the right syntax is for SQL Server 2016, you will only look at sales from that period. Commented Dec 29, 2020 at 21:26
• Thank you Stephen, much appreciated.
– Dave
Commented Dec 29, 2020 at 22:05
• I'm pretty sure that there will be a significant perfomance penalty using two CTEs as opposed to just a straight JOIN - unless you know different? I've made the observation in my own answer but am open to correction! Commented Dec 29, 2020 at 22:50
• In my experience on other platforms (Oracle, PostgreSQL, & SQLite, mostly), CTEs have not impacted performance for good or ill, except when the platform offers a keyword or hint that requires the database to do things in CTE order and that keyword is used. I have no recent experience on SQL Server, so I can't say for sure in this case. The reason is that, for most platforms, the order of operations in the CTE doesn't bind the database's optimizer or planner to actually do things in that order. I used CTEs here because they are much clearer, and I was trying to teach how I built my query. Commented Dec 29, 2020 at 22:58

To answer your question, I did the following (see the fiddle here):

Create and populate tables - you could do this yourself for future questions - it's really helpful, removes duplication of effort and also eliminates copy and paste errors.

I've slightly changed the table definitions more or less in line with the SQL Style Guide - this is my personal preference - my advice is to pick a style and stick to it!

``````CREATE TABLE sales
(
territory_id INT NOT NULL,
transaction_id INT NOT NULL,
transaction_date DATE NOT NULL,
product_code VARCHAR (25) NOT NULL,
amount INT NOT NULL
);
``````

and

``````CREATE TABLE sales_rep
(
territory_id INT NOT NULL,
the_rep VARCHAR (10) NOT NULL,
from_date DATE NOT NULL,
to_date   DATE NOT NULL
);
``````

data:

``````INSERT INTO sales VALUES
(15,           1,              '2020-04-15',      'Bags',            50),
(15,           1,              '2020-04-15',      'Bags',            50),
(15,           2,              '2020-06-02',      'Bags',             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);
``````

and:

``````INSERT INTO sales_rep VALUES
(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');
``````

Then I ran the following query:

``````SELECT
sr.the_rep AS "Rep name", SUM(s.amount) AS "Total sales"
FROM sales s
JOIN sales_rep sr
ON  s.transaction_date >= sr.from_date
AND s.transaction_date <= sr.to_date
WHERE s.transaction_date >= '2020-06-01' AND s.transaction_date <= '2020-06-02'
OR    s.transaction_date >= '2020-06-17' AND s.transaction_date <= '2020-06-30'
GROUP BY sr.the_rep;
``````

Result:

``````Rep name    Total sales
Anne            185
``````

I then used `SET SHOWPLAN_TEXT ON` (see the fiddle) to look at the two proposed (this one and @StephenK's) answers' performance - there is a slight difference in the plans, but I'm not a SQL Server man so I don't know which might be better. In any case, the tables are so small that the optimiser may or may not be using indexes that it might use with a large dataset!

I've run the SQL on different versions using the fiddle and the plans for the different queries remain the same!

I would urge you to test both solutions with a realistic dataset and go from there - reporting back here would be good!

• This is my first post to a forum, so thanks for the advice on how to provide the data and this is test data to prove the concept. The real data extracts will be much larger (eg running into 10 millions of rows) when run over a few months.
– Dave
Commented Dec 30, 2020 at 10:08
• @Dave - you should really test with a realistic dataset - from what you say, you're going to be using this frequently so you have a vested interest in getting it right. I'm doing further research into which query will be the more performant and will get back to you, hopefully before the day is out - on UTC +0 here! Commented Dec 30, 2020 at 10:16
• Thanks Verace. I will mostly likely use a CTE to extract the Sales rep and their start/end dates as I cannot hardcode in the dates (as in the above example) as these date are inserted/updated by another system/department that this extract will be reading the data from.
– Dave
Commented Dec 30, 2020 at 10:24
• @Dave: I agree that this query seems a little too adapted to your specific data sample, but I'd just like to point out that it doesn't really matter whether a query uses CTEs or not – you can parametrise it regardless. The query in this specific answer simply needs the transaction date filter in the WHERE clause to be replaced with the one checking only for the boundaries of the given date range, and also another filter, on the sales rep name, to be added to it. Commented Dec 30, 2020 at 11:06