如何在SQL Server 2014中用Partition计算工作中心实时余额

Hey there! Let's figure out how to fix that running balance issue you're having. The problem with your initial SUM(qty) OVER(PARTITION BY...) approach is probably that you're not accounting for the fact that each transaction affects two work centers (one losing quantity, one gaining) — and you need to track those changes separately to get accurate real-time balances after each transaction.

Step-by-Step Solution #

First, let's break down what we need to do:

• Split each transaction into two separate records: one for the FromWC (with a negative quantity, since it's losing material) and one for the ToWC (with a positive quantity, since it's gaining).
• Use a window function to calculate the running total for each work center, ordered by the transaction sequence (so we get the balance after each transaction completes).
• Join these running balances back to your original transaction table to show both FromWC and ToWC balances in one row.

Example SQL Code #

Let's assume your transaction table is named MaterialTransfers with these columns: TransactionID, FromWC, ToWC, Qty, TransactionDate (we need TransactionDate or TransactionID to ensure transactions are processed in order). Here's the working query:

WITH TransferBreakdown AS (
    -- 记录转出工作中心的数量减少
    SELECT
        TransactionID,
        WorkCenter = FromWC,
        QtyAdjustment = -Qty,
        TransactionDate
    FROM MaterialTransfers
    UNION ALL
    -- 记录转入工作中心的数量增加
    SELECT
        TransactionID,
        WorkCenter = ToWC,
        QtyAdjustment = Qty,
        TransactionDate
    FROM MaterialTransfers
),
RunningBalances AS (
    SELECT
        TransactionID,
        WorkCenter,
        RealTimeBalance = SUM(QtyAdjustment) OVER (
            PARTITION BY WorkCenter
            ORDER BY TransactionDate, TransactionID -- 同时用日期和ID避免排序歧义
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        )
    FROM TransferBreakdown
)
-- 关联回原表，展示每笔交易后的两个工作中心余额
SELECT
    mt.TransactionID,
    mt.FromWC,
    mt.ToWC,
    mt.Qty,
    FromWC_Balance = rb_from.RealTimeBalance,
    ToWC_Balance = rb_to.RealTimeBalance
FROM MaterialTransfers mt
JOIN RunningBalances rb_from 
    ON rb_from.TransactionID = mt.TransactionID 
    AND rb_from.WorkCenter = mt.FromWC
JOIN RunningBalances rb_to 
    ON rb_to.TransactionID = mt.TransactionID 
    AND rb_to.WorkCenter = mt.ToWC
ORDER BY mt.TransactionDate, mt.TransactionID;

Why This Works #

• TransferBreakdown CTE把每笔交易拆成两条记录，让我们可以独立追踪每个工作中心的数量变化。
• RunningBalances CTE用SUM() OVER()，按工作中心分组，按交易日期和ID排序，确保交易按正确顺序处理。ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW明确告诉SQL Server计算从第一笔到当前交易的累计值——这在SQL Server 2014里很重要，默认的RANGE模式在有重复日期时可能会出现异常结果。

Example Results #

如果你的源数据是这样的：

查询会返回预期的实时余额：

补充：如果有初始库存怎么办？ #

如果工作中心一开始就有库存，只需要在TransferBreakdown里加入初始余额记录即可：

WITH TransferBreakdown AS (
    -- 先加入初始库存记录
    SELECT
        TransactionID = 0, -- 确保这条记录排在所有交易前面
        WorkCenter,
        QtyAdjustment = InitialQty,
        TransactionDate = '1900-01-01'
    FROM InitialWorkCenterBalances -- 你的初始库存表
    UNION ALL
    -- ... 原有的TransferBreakdown代码 ...
)

这样计算出的余额就会包含初始库存了。

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