SQL实现周销售额按工作日分摊计算月度收入方案咨询

实现逻辑 #

• 确定每条周销售记录对应的日期范围：以sales_date为周结束日期，往前推6天为周起始日期
• 生成周范围内所有日期，过滤周六、周日得到有效工作日
• 统计单周总工作日数、每周内各月份对应的工作日数，按工作日占比拆分周销售额到对应月份
• 关联1-12月参照表，保证无销售数据的月份也能正常展示，收入记为0
• 支持输入参数@MonthNumber控制返回结果：传0返回全年数据，传对应月份值仅返回单月数据

修正说明 #

你之前编写的周末过滤逻辑里% 3属于笔误，应改为% 7才能正确计算星期，兼容不同@@DATEFIRST配置。

完整SQL代码 #

-- 声明输入参数，可根据需求修改
DECLARE @MonthNumber INT = 0;

WITH WeekDates AS (
    -- 递归生成每条周记录对应的所有日期
    SELECT 
        sales_date,
        payment_sum,
        DATEADD(DAY, -6, sales_date) AS current_date,
        sales_date AS week_end
    FROM #Test
    UNION ALL
    SELECT 
        sales_date,
        payment_sum,
        DATEADD(DAY, 1, current_date),
        week_end
    FROM WeekDates
    WHERE current_date < week_end
),
WorkDayStats AS (
    -- 统计每周总工作日、各月份对应工作日数
    SELECT 
        sales_date,
        payment_sum,
        DATEPART(MONTH, current_date) AS belong_month,
        COUNT(*) AS month_work_days,
        SUM(COUNT(*)) OVER(PARTITION BY sales_date) AS total_work_days
    FROM WeekDates
    -- 兼容不同@@DATEFIRST配置的周末过滤逻辑
    WHERE ((DATEPART(dw, current_date) + @@DATEFIRST) % 7) NOT IN (0, 1)
    GROUP BY sales_date, payment_sum, DATEPART(MONTH, current_date)
),
MonthSplit AS (
    -- 按占比拆分销售额，按月汇总
    SELECT 
        belong_month,
        SUM(payment_sum * 1.0 * month_work_days / total_work_days) AS income
    FROM WorkDayStats
    GROUP BY belong_month
),
AllMonths AS (
    -- 生成1-12月参照表，保证无数据月份也能展示
    SELECT 1 AS month_num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
    SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
)
-- 最终查询输出
SELECT 
    RIGHT('0' + CAST(a.month_num AS VARCHAR(2)), 2) AS [Month],
    ROUND(ISNULL(m.income, 0), 0) AS [Incom]
FROM AllMonths a
LEFT JOIN MonthSplit m ON a.month_num = m.belong_month
WHERE (@MonthNumber = 0 OR a.month_num = @MonthNumber)
ORDER BY a.month_num;

输出效果 #

• 当@MonthNumber = 0时，返回12个月份的全年汇总数据，月份固定为两位字符格式，无销售额的月份收入为0
• 当@MonthNumber = 1时，仅返回1月的月度收入数据

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