SQL Server 2012中按自定义时间段计算生产线小时生产目标

1. 定义自定义时间段 #

先用CTE（或物理表）明确10个生产时间段，同时计算每个时间段的可用生产分钟数：

WITH ShiftPeriods AS (
    SELECT 1 AS PeriodID, CAST('06:00:00' AS TIME) AS PeriodStart, CAST('07:00:00' AS TIME) AS PeriodEnd
    UNION ALL SELECT 2, '07:00:00', '08:00:00'
    UNION ALL SELECT 3, '08:15:00', '09:00:00'
    UNION ALL SELECT 4, '09:00:00', '10:00:00'
    UNION ALL SELECT 5, '10:15:00', '11:00:00'
    UNION ALL SELECT 6, '11:00:00', '12:00:00'
    UNION ALL SELECT 7, '12:30:00', '13:30:00'
    UNION ALL SELECT 8, '13:30:00', '14:30:00'
    UNION ALL SELECT 9, '14:45:00', '15:30:00'
    UNION ALL SELECT 10, '15:30:00', '16:30:00'
),
PeriodDurations AS (
    SELECT 
        PeriodID,
        PeriodStart,
        PeriodEnd,
        DATEDIFF(MINUTE, PeriodStart, PeriodEnd) AS AvailableMinutes
    FROM ShiftPeriods
)

2. 基础场景：固定Takt Time无变更 #

如果每种单元的节拍时间是固定值（无生效时间区间），直接关联节拍时间表计算各单元在每个时间段的生产目标：

SELECT 
    tt.UnitType,
    pd.PeriodID,
    CONCAT(pd.PeriodStart, ' - ', pd.PeriodEnd) AS PeriodRange,
    pd.AvailableMinutes,
    tt.TaktTime, -- 确保单位为「分钟/件」，若为秒则除以60
    ROUND(pd.AvailableMinutes / tt.TaktTime, 2) AS ProductionTarget
FROM PeriodDurations pd
CROSS JOIN TaktTimes tt -- TaktTimes表需包含UnitType和TaktTime字段
ORDER BY tt.UnitType, pd.PeriodID;

3. 进阶场景：Takt Time随时间变更（需拆分时间段计算） #

若存在某个时间段内单元的节拍时间发生变更（比如时间段6中途调整Takt），需先计算时间段与Takt生效区间的交集，拆分计算后再求和：

-- 目标计算日期，可替换为变量或存储过程参数
DECLARE @TargetDate DATE = GETDATE();

WITH ShiftPeriods AS (
    SELECT 
        1 AS PeriodID, 
        CAST(CONCAT(@TargetDate, ' ', '06:00:00') AS DATETIME) AS PeriodStart, 
        CAST(CONCAT(@TargetDate, ' ', '07:00:00') AS DATETIME) AS PeriodEnd
    UNION ALL SELECT 2, CONCAT(@TargetDate, ' 07:00:00'), CONCAT(@TargetDate, ' 08:00:00')
    UNION ALL SELECT 3, CONCAT(@TargetDate, ' 08:15:00'), CONCAT(@TargetDate, ' 09:00:00')
    UNION ALL SELECT 4, CONCAT(@TargetDate, ' 09:00:00'), CONCAT(@TargetDate, ' 10:00:00')
    UNION ALL SELECT 5, CONCAT(@TargetDate, ' 10:15:00'), CONCAT(@TargetDate, ' 11:00:00')
    UNION ALL SELECT 6, CONCAT(@TargetDate, ' 11:00:00'), CONCAT(@TargetDate, ' 12:00:00')
    UNION ALL SELECT 7, CONCAT(@TargetDate, ' 12:30:00'), CONCAT(@TargetDate, ' 13:30:00')
    UNION ALL SELECT 8, CONCAT(@TargetDate, ' 13:30:00'), CONCAT(@TargetDate, ' 14:30:00')
    UNION ALL SELECT 9, CONCAT(@TargetDate, ' 14:45:00'), CONCAT(@TargetDate, ' 15:30:00')
    UNION ALL SELECT 10, CONCAT(@TargetDate, ' 15:30:00'), CONCAT(@TargetDate, ' 16:30:00')
),
UnitTaktPeriods AS (
    SELECT 
        UnitType,
        TaktTime,
        EffectiveStart,
        ISNULL(EffectiveEnd, DATEADD(DAY, 1, @TargetDate)) AS EffectiveEnd
    FROM TaktTimes -- 表需包含UnitType、TaktTime、EffectiveStart、EffectiveEnd字段
    WHERE EffectiveStart < DATEADD(DAY, 1, @TargetDate) 
      AND (EffectiveEnd IS NULL OR EffectiveEnd > @TargetDate)
),
IntersectedPeriods AS (
    SELECT 
        sp.PeriodID,
        utp.UnitType,
        utp.TaktTime,
        -- 取两个区间的最大开始时间作为交集起点
        CASE WHEN sp.PeriodStart > utp.EffectiveStart THEN sp.PeriodStart ELSE utp.EffectiveStart END AS IntersectStart,
        -- 取两个区间的最小结束时间作为交集终点
        CASE WHEN sp.PeriodEnd < utp.EffectiveEnd THEN sp.PeriodEnd ELSE utp.EffectiveEnd END AS IntersectEnd
    FROM ShiftPeriods sp
    JOIN UnitTaktPeriods utp 
        ON sp.PeriodStart < utp.EffectiveEnd 
        AND sp.PeriodEnd > utp.EffectiveStart -- 仅保留有重叠的区间
),
SegmentTargets AS (
    SELECT 
        PeriodID,
        UnitType,
        DATEDIFF(MINUTE, IntersectStart, IntersectEnd) / utp.TaktTime AS SegmentTarget
    FROM IntersectedPeriods utp
)
SELECT 
    UnitType,
    PeriodID,
    CONCAT(FORMAT(MIN(IntersectStart), 'HH:mm'), ' - ', FORMAT(MAX(IntersectEnd), 'HH:mm')) AS PeriodRange,
    ROUND(SUM(SegmentTarget), 2) AS TotalProductionTarget
FROM SegmentTargets
JOIN IntersectedPeriods ip ON SegmentTargets.PeriodID = ip.PeriodID AND SegmentTargets.UnitType = ip.UnitType
GROUP BY UnitType, PeriodID
ORDER BY UnitType, PeriodID;

关键注意事项 #

• 单位匹配：确保TaktTime的单位与可用时间一致（如均为分钟），若TaktTime是秒级，需转换为分钟（TaktTime / 60）。
• 维护便捷性：可将自定义时间段存入物理表（如ShiftPeriods），避免每次查询写大量UNION ALL。
• 取整规则：根据业务需求调整ROUND函数，或使用FLOOR、CEILING处理目标数量的取整逻辑。

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