Get running balance of a work center using partition

Question

I am writing a script that will run on SQL Server 2014.

I have a table of transactions recording transfers from one work center to another. The simplified table is below:

DECLARE @transactionTable TABLE (wono varchar(10),transferDate date
                      ,fromWC varchar(10),toWC varchar(10),qty float)

INSERT INTO @transactionTable
SELECT '0000000123','5/10/2018','STAG','PP-B',10
UNION
SELECT '0000000123','5/11/2018','PP-B','PP-T',5
UNION
SELECT '0000000123','5/11/2018','PP-T','TEST',3
UNION
SELECT '0000000123','5/12/2018','PP-B','PP-T',5
UNION
SELECT '0000000123','5/12/2018','PP-T','TEST',5
UNION
SELECT '0000000123','5/13/2018','PP-T','TEST',2
UNION
SELECT '0000000123','5/13/2018','TEST','FGI',8
UNION
SELECT '0000000123','5/14/2018','TEST','FGI',2

SELECT *, 
    fromTotal = -SUM(qty) OVER(PARTITION BY fromWC ORDER BY wono, transferdate, fromWC),
    toTotal = SUM(qty) OVER(PARTITION BY toWC ORDER BY wono, transferdate, toWC)
FROM @transactionTable
ORDER BY wono, transferDate, fromWC

I want to get a running balance of the fromWC and toWC after each transaction.

Given the records above, the end result should be this:

I believe it is possible to use SUM(qty) OVER(PARTITION BY..., but I am not sure how to write the statement. When I try to get the increase and decrease, each line always results in 0.

How do I write the SUM statement to achieve the desired results?

UPDATE

This image shows each transaction, the resulting WC qty, and highlights the corresponding from and to work centers for each transaction.

For example, looking at the second record on 5/11, 3 were transferred from PP-T to TEST. After the transaction, there were 5 in PP-B, 2 in PP-T, and 3 in TEST.

The best solution will depend on the version of SQL Server. This is a very comprehensive answer: https://stackoverflow.com/a/11313533/6305294 — Alex, May 20 '18 at 21:51
@Alex, I added the version details to the post. I am aware of your suggested prior post, and am already using the `SUM OVER` option. I believe I can get the desired results, I am just not sure how to `partition` and `order` the data to get the desired results. — davids, May 20 '18 at 21:59
Why is `TEST` fromBalance = 2 on "2018-05-11"? What are the starting balances? — Alex, May 20 '18 at 22:12

Alex · Accepted Answer · 2018-05-21T23:47:08.627

I can get close, except starting balances:

SELECT  wono, transferDate, fromWC, toWC, qty,
    SUM( CASE WHEN WC = fromWC THEN RunningTotal ELSE 0 END ) AS FromQTY,
    SUM( CASE WHEN WC = toWC THEN RunningTotal ELSE 0 END ) AS ToQTY
FROM( -- b
    SELECT *, SUM(Newqty) OVER(PARTITION BY WC ORDER BY wono,transferdate, fromWC, toWC) AS RunningTotal
    FROM(-- a
            SELECT wono, transferDate, fromWC, toWC, fromWC AS WC, qty, -qty AS Newqty, 'From' AS RecType
            FROM @transactionTable
            UNION ALL
            SELECT wono, transferDate, fromWC, toWC, toWC AS WC, qty, qty AS Newqty, 'To' AS RecType
            FROM @transactionTable
        ) AS a
    ) AS b
GROUP BY wono, transferDate, fromWC, toWC, qty

My logic assumes that all balances start at 0, therefore "STAG" balance will be -10.

How the query works:

"Unpivot" the input record set into "From" and "To" records with quantities negated for "From" records.
Calculate running totals for each "WC".
Combine "Unpivoted" records back into original shape

Solution 2

WITH CTE
AS(
SELECT *,
    ROW_NUMBER() OVER( ORDER BY wono, transferDate, fromWC, toWC ) AS Sequence
FROM @transactionTable
 ),
 CTE2
 AS( 
 SELECT *,
    fromTotal = -SUM(qty) OVER(PARTITION BY fromWC ORDER BY Sequence),
    toTotal = SUM(qty) OVER(PARTITION BY toWC ORDER BY Sequence)
FROM CTE
 )
SELECT a.Sequence, b.Sequence, c.Sequence, a.wono, a.transferDate, a.fromWC, a.toWC, a.qty, a.fromTotal + ISNULL( b.toTotal, 0 ) AS FromTotal, a.toTotal + ISNULL( c.fromTotal, 0 ) AS ToTotal
FROM CTE2 AS a
    OUTER APPLY( SELECT TOP 1 * FROM CTE2 WHERE wono = a.wono AND Sequence < a.Sequence AND toWC = a.fromWC ORDER BY Sequence DESC ) AS b
    OUTER APPLY( SELECT TOP 1 * FROM CTE2 WHERE wono = a.wono AND Sequence < a.Sequence AND fromWC = a.toWC ORDER BY Sequence DESC ) AS c
ORDER BY a.Sequence

Note: This solution would benefit greatly from an "ID" column, that mirrors transaction order OR at least you will need an index on wono, transferDate, fromWC, toWC

This is an interesting approach. It does produce the desired results and is much closer than I have ever made it. My biggest concern is the performance on a very large table. If someone else doesn't suggest a more efficient method, I will accept this as an answer. — davids, May 21 '18 at 12:01
@davids, I have added another solution. I think both approach would be resource heavy. As to which one would be faster you need to test yourself. — Alex, May 21 '18 at 23:44
Looks like the first method is the most efficient, even with an ordered id column — davids, May 24 '18 at 18:00

Get running balance of a work center using partition

1 Answers1

Solution 2