SQL Server calculate running total value with query

Question

I Use SQL Server 2012 and have a table like below:

DECLARE @T TABLE(Id INT, [Type] CHAR(1), Quantity INT, Price MONEY, UnitPrice AS (Price/Quantity))
INSERT INTO @T VALUES 
    (1, 'I', 30, 1500),
    (2, 'O', 5, NULL),
    (3, 'O', 20, NULL),
    (4, 'O', 2, NULL),
    (5, 'I', 10, 2500),
    (6, 'I', 8, 1000),
    (7, 'O', 3, NULL),
    (8, 'O', 10, NULL),
    (9, 'I', 12, 3600)

In my table I have a Type Column With Values ('I' and 'O') I have unit price for 'I' Type Record and 'O' Type Record used last 'I' Type Record Value I want to calculate RunningTotalPrice (Sum of Quantity*UnitPrice of each rows).

Following code calculate RunningTotalQuantity:

SELECT *, 
        SUM(CASE WHEN [Type] = 'I' Then Quantity ELSE -Quantity END)OVER (ORDER BY Id) AS QuantityRunningTotal
FROM @T

and Results of this query is:

Id  Type    Quantity    Price   UnitPrice   QuantityRunningTotal
1   I       30          1500/00 50/00       30
2   O       5           NULL    NULL        25
3   O       20          NULL    NULL        5
4   O       2           NULL    NULL        3
5   I       10          2500/00 250/00      13
6   I       8           1000/00 125/00      21
7   O       3           NULL    NULL        18
8   O       10          NULL    NULL        8
9   I       12          3600/00 300/00      20

I want to have following Result

Id  Type    Quantity    Price   UnitPrice   QuantityRunningTotal  Price       RunningTotalPrice
1   I       30          1500/00 50/00       30                    1500/00      1500/00
2   O       5           NULL    50/00       25                    250/00       1250/00
3   O       20          NULL    50/00       5                     1000/00      250/00
4   O       2           NULL    50/00       3                     100/00       150/00
5   I       10          2500/00 250/00      13                    2500/00      2650/00
6   I       8           1000/00 125/00      21                    1000/00      3650/00
7   O       3           NULL    125/00      18                    375/00       3275/00
8   O       10          NULL    125/00      8                     1250/00      2025/00
9   I       12          3600/00 300/00      20                    3600/00      5625/00

In this result Null Unitprice Column valued with last exists unitprice in before records. and Calculate Price ( Quantity * UnitPrice) and The Calculate Running Total Of Price.

Second Price Calculated by Quantity*UnitPrice, and equal to first price for records that are 'I' Type. — mehdi lotfi
– mehdi lotfi, Commented Oct 28, 2014 at 8:47

GarethD · Accepted Answer · 2014-10-28 09:19:56Z

1

Unfortunately LEAD and LAG functions can't be used to the last not NULL value, so you would need to use OUTER APPLY to get the previous UnitPrice to use in rows where the type is 'O':

SELECT  t.ID,
        t.[Type],
        t.Quantity,
        t.Price,
        t.UnitPrice, 
        SUM(CASE WHEN t.[Type] = 'I' THEN t.Quantity ELSE -t.Quantity END) OVER (ORDER BY t.Id) AS QuantityRunningTotal,
        CASE WHEN t.[Type] = 'I' THEN t.Price ELSE t.Quantity * p.UnitPrice END AS Price2,
        SUM(CASE WHEN t.[Type] = 'I' THEN t.Price ELSE -t.Quantity * p.UnitPrice END)OVER (ORDER BY t.Id) AS QuantityRunningTotal
FROM    @T AS t
        OUTER APPLY
        (   SELECT  TOP 1 t2.UnitPrice
            FROM    @T AS t2
            WHERE   t2.ID < t.ID
            AND     t2.UnitPrice IS NOT NULL
            ORDER BY t2.ID DESC
        ) AS p;

answered Oct 28, 2014 at 9:19

GarethD

70k11 gold badges90 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mehdi lotfi Over a year ago

Tanks for your answer. My original table have a more than 10 million records and your query is very slow for it. I look for a high performance query. thanks

t-clausen.dk Over a year ago

@mehdilotfi subqueries is usually faster than window functions. Try adding index to ID. Alternatively you could script to fill out the null values

Collectives™ on Stack Overflow

SQL Server calculate running total value with query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related