CODEWITHSUNDEEP
sql-servert-sqlsql-server-2014
davids
davids

Reputation: 5587

Get running balance of a work center using partition

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:

enter image description here

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

enter image description here

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.

Upvotes: 2

Views: 82

Answers (1)

Alex
Alex

Reputation: 5157

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:

  1. "Unpivot" the input record set into "From" and "To" records with quantities negated for "From" records.
  2. Calculate running totals for each "WC".
  3. 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

Upvotes: 1

Related Questions