SQL Server中按5个工作日汇总金额的实现问题（排除周末）

我来帮你搞定这个需求！你现在的查询是按连续5天汇总，但没排除周末，要实现从每个日期开始的5个工作日内（含当天）的金额总和，可以用两种方法来处理，下面详细说明：

方法一：自定义工作日计算函数 #

首先创建一个标量函数，用来计算从指定日期开始，加上N个工作日后的日期。这个函数会自动跳过周六和周日，不受服务器DATEFIRST设置影响：

CREATE FUNCTION dbo.AddWorkdays(@StartDate DATETIME, @Workdays INT)
RETURNS DATETIME
AS
BEGIN
    DECLARE @EndDate DATETIME = @StartDate;
    DECLARE @Count INT = 0;

    WHILE @Count < @Workdays
    BEGIN
        SET @EndDate = DATEADD(DAY, 1, @EndDate);
        -- 用DATENAME判断周末，避免DATEFIRST设置影响
        IF DATENAME(WEEKDAY, @EndDate) NOT IN ('Saturday', 'Sunday')
        BEGIN
            SET @Count = @Count + 1;
        END
    END

    RETURN @EndDate;
END

然后修改你的查询，用这个函数替换原来的连续5天逻辑，同时优化日期判断的方式（替换LIKE为日期范围，更高效准确）：

SELECT 
    t1.AccountID,
    CONVERT(DATE, t1.[Date]) AS [Date],
    SUM(t2.Amount) AS [Sum Amount]
FROM 
    [dbo].[HANMI_ABRIGO_TRANSACTIONS] t1
CROSS APPLY (
    SELECT 
        a.Amount
    FROM 
        [dbo].[HANMI_ABRIGO_TRANSACTIONS] a
    WHERE 
        a.AccountID = t1.AccountID
        -- 限定t2日期在t1当天到第5个工作日之间（含两端）
        AND CONVERT(DATE, a.[Date]) BETWEEN CONVERT(DATE, t1.[Date]) 
            AND dbo.AddWorkdays(CONVERT(DATE, t1.[Date]), 4)
        -- 限定7月份的交易
        AND CONVERT(DATE, a.[Date]) BETWEEN '2021-07-01' AND '2021-07-31'
        AND a.Amount > 0
        -- 确保交易日期是工作日（如果你的数据里只有工作日交易，可以去掉这句）
        AND DATENAME(WEEKDAY, CONVERT(DATE, a.[Date])) NOT IN ('Saturday', 'Sunday')
) t2
WHERE 
    t1.AccountID = '123'
    AND CONVERT(DATE, t1.[Date]) BETWEEN '2021-07-01' AND '2021-07-31'
    AND t1.Amount > 0
    -- 只处理工作日的交易日期
    AND DATENAME(WEEKDAY, CONVERT(DATE, t1.[Date])) NOT IN ('Saturday', 'Sunday')
GROUP BY 
    t1.AccountID,
    CONVERT(DATE, t1.[Date])
ORDER BY 
    CONVERT(DATE, t1.[Date])

方法二：递归CTE生成工作日范围（无需自定义函数） #

如果你的环境不允许创建函数，可以用递归CTE生成每个起始日期对应的5个工作日，再关联交易表求和：

WITH WorkdayRanges AS (
    -- 初始化：每个起始日期作为第1个工作日
    SELECT 
        t1.AccountID,
        CONVERT(DATE, t1.[Date]) AS StartDate,
        CONVERT(DATE, t1.[Date]) AS Workday,
        1 AS WorkdayCount
    FROM 
        [dbo].[HANMI_ABRIGO_TRANSACTIONS] t1
    WHERE 
        t1.AccountID = '123'
        AND CONVERT(DATE, t1.[Date]) BETWEEN '2021-07-01' AND '2021-07-31'
        AND t1.Amount > 0
        AND DATENAME(WEEKDAY, t1.[Date]) NOT IN ('Saturday', 'Sunday')
    
    -- 递归生成后续的工作日，直到凑够5个
    UNION ALL
    
    SELECT 
        wr.AccountID,
        wr.StartDate,
        DATEADD(DAY, 1, wr.Workday) AS Workday,
        wr.WorkdayCount + 1 AS WorkdayCount
    FROM 
        WorkdayRanges wr
    WHERE 
        wr.WorkdayCount < 5
        AND DATENAME(WEEKDAY, DATEADD(DAY, 1, wr.Workday)) NOT IN ('Saturday', 'Sunday')
)

-- 关联交易表，计算每个起始日期的总和
SELECT 
    wr.AccountID,
    wr.StartDate AS [Date],
    SUM(t2.Amount) AS [Sum Amount]
FROM 
    WorkdayRanges wr
JOIN 
    [dbo].[HANMI_ABRIGO_TRANSACTIONS] t2 
        ON wr.AccountID = t2.AccountID 
        AND CONVERT(DATE, t2.[Date]) = wr.Workday
        AND t2.Amount > 0
GROUP BY 
    wr.AccountID,
    wr.StartDate
ORDER BY 
    wr.StartDate;

关键说明 #

• 两种方法都会自动跳过周六和周日，确保计算的是真正的5个工作日（比如周五开始的话，会包含周五、下周一到周四）。
• 替换了原来的LIKE '2021-07%'为日期范围比较，避免字符串匹配的性能问题和格式歧义。
• 去掉了原查询中多余的t1.amount字段和DISTINCT，因为GROUP BY已经会按AccountID和Date聚合，和你需要的输出结构一致。

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