CODEWITHSUNDEEP
sql-servert-sqlsql-server-2016
user8023823
user8023823

Reputation:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW only working on first 15 rows?

I'm trying to get a rolling total per account in our system. So each transaction add/subtracts line by line

I'm Attempting to use the unbound preceding functionality in SQL 2016.

, SUM(isnull(Value,0.00)) OVER(ORDER BY AccountNo ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Total

Currently this works up until line 15 then it just starts a rolling total of every transaction, I've tried varying combinations of the Unbounded function but always get the same result?

enter image description here

Upvotes: 0

Views: 3829

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

If you want a rolling sum per account, then you should be using this syntax:

SELECT
    AccountNo,
    TrackingValue,
    mk,
    SUM(TrackingValue) OVER (PARTITION BY AccountNo ORDER BY mk) AS RollingSum
FROM yourTable
ORDER BY
    AccountNo,
    mk;

Note that the default window for SUM(), when used as an analytic function, is already ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so you do not need to specify this explicitly if you just want an unbounded rolling sum.

Upvotes: 1

Related Questions