I wonder if someone can help me with this SQL Server 2016 query.
I have two tables with following data
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.
This is the table that holds the sales made by territory
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.