SQL Server 2017 (Linux)

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


(No column name)


Microsoft SQL Server 2017 (RTM-CU22) (KB4577467) - 14.0.3356.20 (X64) 
	Aug 20 2020 22:33:27 
	Copyright (C) 2017 Microsoft Corporation
	Express Edition (64-bit) on Linux (CentOS Linux 7 (Core))


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

CREATE INDEX transaction_id_ix ON sales (transaction_id)
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);
9 rows affected

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
);
CREATE INDEX the_rep_ix ON sales_rep (the_rep);
CREATE INDEX sr_from_date_ix ON sales_rep (from_date);
CREATE INDEX sr_to_date_ix ON sales_rep (to_date);
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');
4 rows affected

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;


Rep name
Total sales


Anne
185


WITH
 territory_periods AS (
SELECT the_rep, territory_id, from_date, to_date
  FROM sales_rep 
 WHERE the_rep = 'Anne'
)
, the_sales AS (
SELECT p.the_rep, s.amount
  FROM sales s
  JOIN territory_periods p
    ON s.territory_id = p.territory_id 
   AND s.transaction_date BETWEEN p.from_date AND p.to_date
)
SELECT the_rep AS "Rep name", SUM(amount) AS "Total sales"
  FROM the_sales 
GROUP
    BY the_rep
;


Rep name
Total sales


Anne
185


SET SHOWPLAN_TEXT ON;
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;


StmtText


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;




StmtText


  |--Stream Aggregate(GROUP BY:([sr].[the_rep]) DEFINE:([Expr1004]=SUM([fiddle_8767691791e041b6ae75cea9c8fa627a].[dbo].[sales].[amount] as [s].[amount])))

       |--Nested Loops(Inner Join, WHERE:([fiddle_8767691791e041b6ae75cea9c8fa627a].[dbo].[sales].[transaction_date] as [s].[transaction_date]>=[fiddle_8767691791e041b6ae75cea9c8fa627a].[dbo].[sales_rep].[from_date] as [sr].[from_date] AND [fiddle_8767691791e041b6ae75cea9c8fa627a].[dbo].[sales].[transaction_date] as [s].[transaction_date]<=[fiddle_8767691791e041b6ae75cea9c8fa627a].[dbo].[sales_rep].[to_date] as [sr].[to_date]))

            |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1002]))

            |    |--Index Scan(OBJECT:([fiddle_8767691791e041b6ae75cea9c8fa627a].[dbo].[sales_rep].[the_rep_ix] AS [sr]), ORDERED FORWARD)

            |    |--RID Lookup(OBJECT:([fiddle_8767691791e041b6ae75cea9c8fa627a].[dbo].[sales_rep] AS [sr]), SEEK:([Bmk1002]=[Bmk1002]) LOOKUP ORDERED FORWARD)

            |--Table Scan(OBJECT:([fiddle_8767691791e041b6ae75cea9c8fa627a].[dbo].[sales] AS [s]), WHERE:([fiddle_8767691791e041b6ae75cea9c8fa627a].[dbo].[sales].[transaction_date] as [s].[transaction_date]>='2020-06-01' AND [fiddle_8767691791e041b6ae75cea9c8fa627a].[dbo].[sales].[transaction_date] as [s].[transaction_date]<='2020-06-02' OR [fiddle_8767691791e041b6ae75cea9c8fa627a].[dbo].[sales].[transaction_date] as [s].[transaction_date]>='2020-06-17' AND [fiddle_8767691791e041b6ae75cea9c8fa627a].[dbo].[sales].[transaction_date] as [s].[transaction_date]<='2020-06-30'))


WITH
 territory_periods AS (
SELECT the_rep, territory_id, from_date, to_date
  FROM sales_rep 
 WHERE the_rep = 'Anne'
)
, the_sales AS (
SELECT p.the_rep, s.amount
  FROM sales s
  JOIN territory_periods p
    ON s.territory_id = p.territory_id 
   AND s.transaction_date BETWEEN p.from_date AND p.to_date
)
SELECT the_rep, Sum(amount)
  FROM the_sales 
GROUP
    BY the_rep
;


StmtText


WITH
 territory_periods AS (
SELECT the_rep, territory_id, from_date, to_date
  FROM sales_rep 
 WHERE the_rep = 'Anne'
)
, the_sales AS (
SELECT p.the_rep, s.amount
  FROM sales s
  JOIN territory_periods p
    ON s.territory_id = p.territory_id 
   AND s.transaction_date BETWEEN p.from_date AND p.to_date
)
SELECT the_rep, Sum(amount)
  FROM the_sales 
GROUP
    BY the_rep
;




StmtText


  |--Stream Aggregate(DEFINE:([Expr1005]=SUM([fiddle_8767691791e041b6ae75cea9c8fa627a].[dbo].[sales].[amount] as [s].[amount]), [fiddle_8767691791e041b6ae75cea9c8fa627a].[dbo].[sales_rep].[the_rep]=ANY([fiddle_8767691791e041b6ae75cea9c8fa627a].[dbo].[sales_rep].[the_rep])))

       |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1002], [s].[territory_id], [s].[transaction_date]))

            |--Nested Loops(Inner Join, OUTER REFERENCES:([s].[transaction_date]))

            |    |--Table Scan(OBJECT:([fiddle_8767691791e041b6ae75cea9c8fa627a].[dbo].[sales] AS [s]))

            |    |--Index Seek(OBJECT:([fiddle_8767691791e041b6ae75cea9c8fa627a].[dbo].[sales_rep].[sr_from_date_ix]), SEEK:([fiddle_8767691791e041b6ae75cea9c8fa627a].[dbo].[sales_rep].[from_date] <= [fiddle_8767691791e041b6ae75cea9c8fa627a].[dbo].[sales].[transaction_date] as [s].[transaction_date]) ORDERED FORWARD)

            |--RID Lookup(OBJECT:([fiddle_8767691791e041b6ae75cea9c8fa627a].[dbo].[sales_rep]), SEEK:([Bmk1002]=[Bmk1002]),  WHERE:([fiddle_8767691791e041b6ae75cea9c8fa627a].[dbo].[sales].[territory_id] as [s].[territory_id]=[fiddle_8767691791e041b6ae75cea9c8fa627a].[dbo].[sales_rep].[territory_id] AND [fiddle_8767691791e041b6ae75cea9c8fa627a].[dbo].[sales].[transaction_date] as [s].[transaction_date]<=[fiddle_8767691791e041b6ae75cea9c8fa627a].[dbo].[sales_rep].[to_date] AND [fiddle_8767691791e041b6ae75cea9c8fa627a].[dbo].[sales_rep].[the_rep]='Anne') LOOKUP ORDERED FORWARD)