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 ROW
or 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 ROW
and 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 ROW
, 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