SQL存储过程实现StartTime填充：按积分分配工作日时段

嘿，这个需求我之前做类似的时段分配功能时碰到过，刚好可以给你一套完整的实现方案，咱们一步步拆解来做：

• 先锁定基准时间：就是已经填好的那条积分最高记录的8:00 AM，这是我们分配时段的起始点
• 给所有记录按积分从高到低排序，分配一个偏移序号（从0开始，积分最高的那条偏移为0，已经有时间了）
• 每个工作日能容纳18个时段（8:00到17:00，每30分钟一个，刚好(17-8)*2=18个），所以用偏移数除以18得到需要往后推的工作日天数，余数就是当天的第几个时段
• 计算目标日期时要自动跳过周末，不能直接加天数，得用专门的工作日计算逻辑
• 最后把目标日期加上对应时段的30分钟偏移，得到每条记录的StartTime

CREATE PROCEDURE FillStartTimeByPoints
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 获取基准时间（积分最高的记录的StartTime，确保是工作日8:00 AM）
    DECLARE @BaseTime DATETIME;
    SELECT TOP 1 @BaseTime = StartTime
    FROM YourTableName
    WHERE StartTime IS NOT NULL
    ORDER BY Points DESC;

    -- 容错：如果没有找到已填充的基准记录，抛出错误
    IF @BaseTime IS NULL
    BEGIN
        RAISERROR('未找到已填充的基准StartTime记录，请确保积分最高的记录已设置StartTime', 16, 1);
        RETURN;
    END

    -- 2. 按积分排序并分配偏移序号
    WITH RankedRecords AS (
        SELECT 
            FirstName,
            LastName,
            Points,
            StartTime,
            -- 偏移序号从0开始，积分最高的为0（已有时间），后续依次递增
            ROW_NUMBER() OVER (ORDER BY Points DESC) - 1 AS OffsetNumber
        FROM YourTableName
    ),
    -- 3. 计算每个记录的工作日偏移和时段索引
    CalculatedTimes AS (
        SELECT
            *,
            OffsetNumber / 18 AS WorkDaysToAdd, -- 需要跳过的工作日数量
            OffsetNumber % 18 AS SlotIndex       -- 当天的时段位置（0-17）
        FROM RankedRecords
    )
    -- 4. 更新未填充的StartTime字段
    UPDATE yt
    SET yt.StartTime = 
        -- 拼接目标日期和时段时间：先拿到目标工作日，再加上对应时段的30分钟偏移
        DATEADD(MINUTE, ct.SlotIndex * 30, 
            dbo.GetNextWorkDay(DATEADD(DAY, ct.WorkDaysToAdd, CAST(@BaseTime AS DATE)), 0) 
            + CAST('08:00:00' AS TIME)
        )
    FROM YourTableName yt
    JOIN CalculatedTimes ct 
        ON yt.FirstName = ct.FirstName 
        AND yt.LastName = ct.LastName 
        AND yt.Points = ct.Points
    WHERE yt.StartTime IS NULL; -- 只更新未填充的记录
END
GO

-- 辅助函数：计算指定日期之后的第N个工作日（自动跳过周六、周日）
CREATE FUNCTION dbo.GetNextWorkDay(@StartDate DATE, @DaysToAdd INT)
RETURNS DATETIME
AS
BEGIN
    DECLARE @ResultDate DATE = @StartDate;
    DECLARE @AddedDays INT = 0;

    WHILE @AddedDays < @DaysToAdd
    BEGIN
        SET @ResultDate = DATEADD(DAY, 1, @ResultDate);
        -- 注意：DATEPART(dw)的取值取决于服务器星期设置，这里假设周日=7，周六=1
        -- 如果你的服务器设置周一为1，需要把判断改成 DATEPART(dw, @ResultDate) IN (6,7)
        IF DATEPART(dw, @ResultDate) NOT IN (1,7)
        BEGIN
            SET @AddedDays = @AddedDays + 1;
        END
    END

    RETURN CAST(@ResultDate AS DATETIME);
END
GO

• 基准时间容错：避免因为没有已填充的记录导致存储过程报错，提前做了判断
• 偏移序号计算：用ROW_NUMBER()生成排序后的序号，减1让第一条（积分最高）的偏移为0，刚好对应已有的基准时间
• 工作日处理：自定义的GetNextWorkDay函数会自动跳过周末，确保分配的都是工作日；注意要根据自己服务器的星期设置调整判断条件（比如有些服务器周一为每周第一天）
• 时段计算：每个时段是30分钟，SlotIndex *30分钟加到当天8:00上，最后一个时段是16:30，加30分钟刚好到17:00，完全符合需求
• 更新范围：只更新StartTime IS NULL的记录，不会覆盖已经存在的基准时间

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