在SSMS中排除周末拆分周度销售数据至日度并按月汇总

问题分析 #

你需要将周度销售数据拆分到日度，仅在工作日分配销售额（周末为0），并按月份汇总。原代码的核心问题在于：

• 按7天分摊销售额而非5个工作日
• 未排除周末日期的销售额分配

修正后的代码 #

DECLARE @min_week_end DATE = (SELECT MIN(dt) FROM ds);
DECLARE @max_week_end DATE = (SELECT MAX(dt) FROM ds);
DECLARE @start_date DATE = DATEADD(DAY, -6, @min_week_end);
DECLARE @end_date DATE = @max_week_end;

WITH date_range AS (
    -- 生成所有需要处理的日期（从第一周的第一天到最后一周的最后一天）
    SELECT @start_date AS dt
    UNION ALL
    SELECT DATEADD(DAY, 1, dt) FROM date_range WHERE dt < @end_date
)
SELECT 
    MONTH(dr.dt) AS MonthNumber,
    ROUND(SUM(CASE 
        -- 仅工作日分配销售额（周末为0）
        WHEN DATEPART(WEEKDAY, dr.dt) NOT IN (1, 7) THEN ds.summ / 5.0 
        ELSE 0 
    END), 2) AS overall_sales
FROM date_range dr
-- 将每个日期关联到对应的周度销售记录
JOIN ds ON dr.dt BETWEEN DATEADD(DAY, -6, ds.dt) AND ds.dt
GROUP BY MONTH(dr.dt)
ORDER BY MonthNumber
OPTION (MAXRECURSION 0);

代码说明 #

1. 日期范围生成：通过CTE生成从第一周起始日到最后周结束日的所有日期，确保覆盖所有需要拆分的日期。
2. 工作日判断：使用DATEPART(WEEKDAY, dr.dt)识别周末（1=周日，7=周六），仅工作日分配周度销售额的1/5。
3. 关联周度数据：将每个日期与对应的周度销售记录关联，确保每个日期分到正确的周销售额。
4. 月度汇总：按月份分组，汇总每日销售额得到各月总销售。

输出结果 #

执行后将得到如下结果：

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