主页 / dba / 问题 / 282313
Accepted
Dave
Asked: 2020-12-30 11:33:35 +0800 CST

无法对销售额字段的值求和 - 各种条件、销售代表 ID 和日期范围

  • 772

我想知道是否有人可以帮助我处理这个 SQL Server 2016 查询。

我有两个包含以下数据的表

销售代表

这是一个表格，其中包含销售代表所关注的地区的历史以及他们所关注的日期范围。

区域 ID 销售代表 从日期 迄今为止
15 安妮 2020-01-01 2020-06-02
15 戴夫 2020-06-03 2020-06-16
15 安妮 2020-06-17 2020-06-22
18 安妮 2020-06-23 2999-12-31

tbl销售额

这是包含地区销售额的表格

区域 ID 交易 ID 交易日期 产品代码 数量
15 1 2020-04-15 包 50
15 1 2020-04-15 包 50
15 2 2020-06-02 包 25
15 3 2020-06-03 鞋 60
15 4 2020-06-07 鞋 10
15 5 2020-06-17 包 15
15 6 2020-06-18 包 25
15 7 2020-06-25 外套 100
18 8 2020-06-27 包 20

我正在尝试生成一份报告，显示给定 SalesRep 的 SalesRep 和两个日期之间的总销售额。

例如，如果我提供了一个 SalesRep = 'Anne' 并且日期范围是 '1 Jun 2020' 到 '30 Jun 2020'，我希望得到的结果是：

SalesRep    Sum
Anne        185

这是因为它应该排除在 2020 年 6 月 3 日至 2020 年 6 月 16 日之间进行的任何交易，因为该地区由戴夫照管了几个星期，并且他在这些日期之间进行了该业务。

我在加入 TblSalesRep 时尝试使用 SUM() 函数，但这些数字不是我所期望的。

sql-server sql-server-2016

2 个回答

  1. Best Answer

    有更紧凑的方法来编写它，但这说明了一种方法。

    首先选择 Anne 负责该地区的地区和时期。

    然后提取交易日期在 Anne 覆盖进行销售的区域的某个时期内的所有销售。

    然后总结销售额。

    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
;
    • 1

  2. 为了回答您的问题，我执行了以下操作（请参见此处的小提琴）：

    创建和填充表格 - 您可以自己为将来的问题执行此操作 - 这真的很有帮助，消除了重复工作，还消除了复制和粘贴错误。

    我根据SQL 样式指南或多或少地稍微更改了表定义——这是我个人的偏好——我的建议是选择一种风格并坚持下去！

    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 TABLE sales_rep
(
  territory_id INT NOT NULL,
  the_rep VARCHAR (10) NOT NULL,
  from_date DATE NOT NULL,
  to_date   DATE NOT NULL
);

    数据：

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

    和：

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

    然后我运行了以下查询：

    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

    然后我用SET SHOWPLAN_TEXT ON（见小提琴）查看了两个提议的（这个和@StephenK's）答案的性能 - 计划略有不同，但我不是 SQL Server 人，所以我不知道哪个可能会更好。在任何情况下，表都非常小，以至于优化器可能使用也可能不使用它可能用于大型数据集的索引！

    我已经使用小提琴在不同版本上运行 SQL，并且不同查询的计划保持不变！

    我会敦促您使用真实的数据集测试这两种解决方案，然后从那里开始 - 在这里报告会很好！

    • 0

相关问题