Running total with purchases and sales in query column

Question

I have a table like below:

How can I have a column like below using Transact-SQL (Order By Date)?

I'm using SQL Server 2016.

That's not previous value, it's running total (with negative values) -- and the values in your example change (400 vs 600) — James Z, Aug 26 '18 at 15:37

score 5 · Accepted Answer · edited Aug 26 '18 at 15:56

The thing you need is called an aggregate windowing function, specifically SUM ... OVER.

The problem is that a 'running total' like this only makes sense if you can specify the order of the rows deterministically. The sample data does not include an attribute that could be used to provide this required ordering. Tables, by themselves, do not have an explicit order.

If you have something like an entry date column, a solution like the following would work:

DECLARE @T table
(
    EntryDate datetime2(0) NOT NULL,
    Purchase money NULL,
    Sale money NULL
);

INSERT @T
    (EntryDate, Purchase, Sale)
VALUES
    ('20180801 13:00:00', $1000, NULL),
    ('20180801 14:00:00', NULL, $400),
    ('20180801 15:00:00', NULL, $400),
    ('20180801 16:00:00', $5000, NULL);

SELECT
    T.Purchase,
    T.Sale,
    Remaining =
        SUM(ISNULL(T.Purchase, $0) - ISNULL(T.Sale, 0)) OVER (
            ORDER BY T.EntryDate
            ROWS UNBOUNDED PRECEDING)
FROM @T AS T;

Demo: db<>fiddle

Using ROWS UNBOUNDED PRECEDING in the window frame is shorthand for ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The behaviour of ROWS is different w.r.t duplicates (and generally better-performing) than the default RANGE. There are strong arguments to say that ROWS ought to have been the default, but that is not what we were given .

For more information see How to Use Microsoft SQL Server 2012's Window Functions by Itzik Ben-Gan, and his excellent book on the topic.

Running total with purchases and sales in query column

1 Answers1