How to calculate Opening and Closing quantity Balances SQL Server

Question

I have the following table strucature

TransactionDate   Item  RecQty  IssueQty

1-jun-2011         A      10      0
2-jun-2011         A      15      0
3-jun-2011         A      20      0
4-jun-2011         A       0     20
4-jun-2011         A       0     20

And I want the result on specific Date i.e 2-jun-2011

 Item    Opening     RecQty  IssueQty  BalanceQty
  A       10            15     0         25

And on 4-Jan-2011

 Item    Opening     RecQty  IssueQty  BalanceQty
  A       45            0     40         5

possible duplicate of [Calculate a Running Total in SqlServer](http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver) — JNK, Jul 15 '11 at 15:40
On 04-Jun, shouldn't the Opening quantity be 10+15+20? RecQty = 0+0, and IssueQty = 20+20? (two 4th Jun records). Which makes the BalanceQty = 5? — MatBailie, Jul 15 '11 at 15:41
Yes you are right Dems the Issueqty should be 40 Now I changed the second result — Shamim, Jul 15 '11 at 16:15

score 1 · Accepted Answer · answered Jul 15 '11 at 16:25

I'm going to assume at least SQL Server 2005. In the future, please specify/tag the question with the minimum version you need to support.

CREATE TABLE #x
(
    TransactionDate SMALLDATETIME,
    Item CHAR(1), RecQty INT, IssueQty INT
);

INSERT #x SELECT '20110601','A',10,0
UNION ALL SELECT '20110602','A',15,0
UNION ALL SELECT '20110603','A',20,0
UNION ALL SELECT '20110604','A',0,20
UNION ALL SELECT '20110604','A',0,20;

DECLARE @StartDate SMALLDATETIME = '20110601', 
        @Date      SMALLDATETIME = '20110602';

WITH x(Item, prevR, prevI, curR, curI) AS
(
    SELECT 
        Item,
        SUM(CASE WHEN TransactionDate < @Date THEN RecQty ELSE 0 END),
        SUM(CASE WHEN TransactionDate < @Date THEN IssueQty ELSE 0 END),
        SUM(CASE WHEN TransactionDate = @Date THEN RecQty ELSE 0 END),
        SUM(CASE WHEN TransactionDate = @Date THEN IssueQty ELSE 0 END)
    FROM #x WHERE TransactionDate BETWEEN @StartDate AND @Date
    GROUP BY Item
)
SELECT 
    Item,
    Opening = prevR - prevI,
    RecQty = curR,
    IssueQty = curI,
    BalanceQty = (prevR - prevI) + (curR - curI)
FROM x;

DROP TABLE #x;

How to calculate Opening and Closing quantity Balances SQL Server

1 Answers1

Linked