windows - Running total with over clause on SQL Server 2014

Question

I use the following T-SQL sub-query to calculate running total quantity by RefId.

As my table has more than 2M of rows I would like to rewrite the query with using an OVER clause.

But I can't get the same result; when CreationDate is not unique for RefId, the result is not correct because the previous row with same date is added to the sum.

Is there a solution to get the correct result?

Sample data set:

CREATE TABLE Command 
(
    CreationDate SMALLDATETIME,
    RefId INT,
    Qte INT
);


INSERT INTO Command VALUES('2021-01-10', 100, 10);
INSERT INTO Command VALUES('2021-01-11', 100, 20);
INSERT INTO Command VALUES('2021-01-11', 100, 60);
INSERT INTO Command VALUES('2021-01-11', 100, 10);
INSERT INTO Command VALUES('2021-01-12', 100, 20);
INSERT INTO Command VALUES('2021-01-10', 200, 20);
INSERT INTO Command VALUES('2021-01-11', 200, 10);
INSERT INTO Command VALUES('2021-01-12', 200, 20);
INSERT INTO Command VALUES('2021-01-12', 200, 10);

Subquery with correct result

SELECT 
    c1.*,
    (SELECT SUM(c2.Qte) 
     FROM Command c2
     WHERE c1.RefId = c2.RefId
       AND c2.CreationDate < c1.CreationDate) AS RunninTotal_Qte
FROM 
    Command c1
ORDER BY 
    c1.RefId, c1.CreationDate

Query rewrite with OVER clause; result is incorrect

SELECT 
    c1.*,
    SUM(c1.Qte) OVER (PARTITION BY c1.RefId ORDER BY c1.CreationDate 
                      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS RunningTotal_Qte
FROM 
    Command c1
ORDER BY 
    c1.RefId, c1.CreationDate

question from:https://stackoverflow.com/questions/65889815/running-total-with-over-clause-on-sql-server-2014

Answer 1

the way you are already doing it is more performant anyway if you must,here is how to do it using only window functions:

SELECT c1.*,
  SUM(c1.Qte) OVER (PARTITION BY c1.RefId ORDER BY c1.CreationDate) 
    - SUM(c1.Qte) OVER (PARTITION BY c1.RefId, c1.CreationDate ORDER BY c1.CreationDate) AS RunningTotal_Qte
FROM Command c1
ORDER BY c1.RefId, c1.CreationDate