如何基于Sales与logSales表生成指定日期范围的销售生产率报表

根据你的需求描述，核心是要统计指定日期内所有销售创建和修改操作带来的margin净变化——新增销售贡献初始margin，修改操作则贡献margin的变动值（variance）。下面是具体的实现思路和SQL代码：

核心逻辑分析 #

你的logSales表已经完整记录了所有关键操作：

• 每条初始销售创建记录的variance=0，对应的margin就是该销售带来的初始生产率
• 每条修改记录的variance直接反映了本次修改导致的margin变化（正为增加，负为减少）

所以我们只需要针对指定日期范围的logSales记录，按以下规则求和：

• 初始记录（variance=0）：计入其margin值
• 修改记录（variance≠0）：计入其variance值

这个逻辑完全匹配你给出的示例：

• 3月报表：仅包含A1的初始记录，求和得150.00
• 5月报表：包含A2的初始记录（+200.00）和A1的修改记录（-60.00），求和得140.00，总方差为-60.00

SQL实现代码 #

1. 总统计（全产品汇总） #

-- 设置查询的日期范围
DECLARE @StartDate DATE = '2018-05-01';
DECLARE @EndDate DATE = '2018-05-31';

SELECT
    SUM(CASE WHEN variance = 0 THEN margin ELSE variance END) AS total_actual_productivity,
    SUM(CASE WHEN variance != 0 THEN variance ELSE 0 END) AS total_variance
FROM logSales
WHERE dateModified BETWEEN @StartDate AND @EndDate;

2. 按产品分组统计 #

如果需要查看每个产品的具体数据，可以加上GROUP BY productID：

DECLARE @StartDate DATE = '2018-05-01';
DECLARE @EndDate DATE = '2018-05-31';

SELECT
    productID,
    SUM(CASE WHEN variance = 0 THEN margin ELSE variance END) AS actual_productivity,
    SUM(CASE WHEN variance != 0 THEN variance ELSE 0 END) AS product_variance
FROM logSales
WHERE dateModified BETWEEN @StartDate AND @EndDate
GROUP BY productID;

补充说明 #

• 为什么不需要关联Sales表？因为logSales已经完整记录了所有创建和修改操作的历史，所有需要的统计数据都可以直接从这里获取，无需依赖Sales表的最新状态。
• 如果你的logSales表中variance字段可能存在空值，可以调整CASE语句为WHEN ISNULL(variance, 0) = 0来兼容这种情况。

内容的提问来源于stack exchange，提问作者Harro