I need some help with windowing functions. I know you can calculate the sum within a window and the running total within a window. But is it possible to calculate the previous running total i.e. the running total not including the current row?
I assume you would need to use the
RANGE argument. I know there is a
CURRENT ROW option but I would need
CURRENT ROW - 1, which is invalid syntax. My knowledge of the
RANGE arguments is limited so any help would be gratefully received.
I know that there are many solutions to this problem, but I am looking to understand the
RANGE arguments and I assume the problem can be cracked with these. I have included one possible way to calculate the previous running total but I wonder if there is a better way:
USE AdventureWorks2012 SELECT s.SalesOrderID , s.SalesOrderDetailID , s.OrderQty , SUM(s.OrderQty) OVER (PARTITION BY SalesOrderID) AS RunningTotal , SUM(s.OrderQty) OVER (PARTITION BY SalesOrderID ORDER BY SalesOrderDetailID) - s.OrderQty AS PreviousRunningTotal -- Sudo code - I know this does not work --, SUM(s.OrderQty) OVER (PARTITION BY SalesOrderID -- ORDER BY SalesOrderDetailID -- ROWS BETWEEN UNBOUNDED PRECEDING -- AND CURRENT ROW - 1) -- AS SudoCodePreviousRunningTotal FROM Sales.SalesOrderDetail s WHERE SalesOrderID IN (43670, 43669, 43667, 43663) ORDER BY s.SalesOrderID , s.SalesOrderDetailID , s.OrderQty