SQL Server 2012 Windowing function to calculate a running total

Question

I need some help with windowing functions.

I have been playing around with sql 2012 windowing functions recently. I know that you can calculate the sum within a window and the running total within a window. But i was wondering; 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 and I know there is a CURRENT ROW option but I would need a CURRENT ROW - I 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

Thanks in advance

Flaxman

Please don't cross post on both database administrators and here except if the answer might be different from a programmers vs dba perspective. – Zobe

Answer 1

You could subtract the current row's value:

SUM(s.OrderQty) OVER (PARTITION BY SalesOrderID
                      ORDER BY SalesOrderDetailID) - s.OrderQty

Or according to the syntax at MSDN and ypercube's answer:

<window frame preceding> ::= 
{
    UNBOUNDED PRECEDING
  | <unsigned_value_specification> PRECEDING
  | CURRENT ROW
}

-->

SUM(s.OrderQty) OVER (PARTITION BY SalesOrderID
                      ORDER BY SalesOrderDetailID
                      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)

Winner