Calculate running totals in SQL Server

You want to show details for the individual orders traded (or transactions), but you also need to show the total orders traded by each trader, in the same result set. This means you need to calculate running totals. There are numerous ways to calculate running totals with tSQL, but you've got to be cautious, or your performance could be less than acceptable. In this post, I'm giving you just a quick example for calculating the running total by using a correlated sub query... and we're going to return it for each trader, by date.

-- first let's create a table for our traders

IF OBJECT_ID('tempdb..#traders') <> 0
DROP TABLE #traders;
CREATE TABLE #Traders (
    TraderID INT IDENTITY(1,1),
    TraderName VARCHAR(45)
 )
SET IDENTITY_INSERT #Traders ON
INSERT #Traders (TraderID,TraderName)
VALUES (10, 'John Doe'),
    (11, 'Jane Smith');
SET IDENTITY_INSERT #Traders OFF;

-- create our orders table 
CREATE TABLE #orders (
OrderID INT IDENTITY(1,1), 
OrderDate DATETIME, 
TraderID INT,
Quantity INT,
RunningTotal INT NULL )

-- populate #orders
SET IDENTITY_INSERT #orders ON
INSERT #orders (OrderID,OrderDate,TraderID,Quantity)
VALUES (1,'10/12/2014 09:00', 10, 100),
            (2, '10/12/2014 11:00', 10, 200),
           (3, '10/12/2014 12:00', 10, 300),
      (4, '10/12/2014 14:00', 11, 400),
           (5, '10/12/2014 14:30', 11, 500),
          (6,'10/13/2014 09:00', 10, 100),
          (7, '10/13/2014 11:00', 10, 90),
          (8, '10/13/2014 12:00', 11, 300),
          (9, '10/13/2014 14:00', 11, 4500),
        (10, '10/13/2014 14:30', 11, 500);

-- take a look at the orders data that you just loaded
SELECT * FROM #orders

   OrderID   OrderDate             TraderID Quantity RunningTotal
     1    2014-10-12 09:00:00.000   10       100       NULL
     2    2014-10-12 11:00:00.000   10       200       NULL
     3    2014-10-12 12:00:00.000   10       300       NULL
     4    2014-10-12 14:00:00.000   11       400       NULL 
     5    2014-10-12 14:30:00.000   11       500       NULL
     6    2014-10-13 09:00:00.000   10       100       NULL
   .............................
   ..................

Now we'll use a correlated subquery to bring it back out for each trader, and each order date.

   -- bring back the running total
   SELECT
          CONVERT(CHAR(10),o.OrderDate,101) [OrderDate],
          t.TraderName,
          o.Quantity,
         (
              SELECT SUM(Quantity) FROM #orders 
              WHERE OrderID <= O.OrderID 
              AND CONVERT(CHAR(10),OrderDate,101) = CONVERT(CHAR(10),o.OrderDate,101)
              AND TraderID = o.TraderID
         ) [Running Total] 
    FROM
          #orders o INNER JOIN #Traders t
          ON o.TraderID = t.TraderID
    ORDER BY
       OrderDate,
       t.TraderName

Here's your full result-set :

    OrderDate    TraderName   Quantity  RunningTotal
    10/12/2014 Jane Smith 400 400
    10/12/2014 Jane Smith 500 900
    10/12/2014 John Doe 100 100
    10/12/2014 John Doe 200 300
    10/12/2014 John Doe 300 600
    10/13/2014 Jane Smith 300 300
    10/13/2014 Jane Smith 4500 4800
    10/13/2014 Jane Smith 500 5300
    10/13/2014 John Doe 100 100
    10/13/2014 John Doe 90 190


That's it!  As I said, there are numerous ways to calculate running totals. There are new functions with the newer releases, such as the OVER clause that became available in v2005.  Here is a quick example using the OVER clause:

   -- using the OVER clause
   SELECT 
      OrderDate,
      Quantity,
      SUM(Quantity) OVER (ORDER BY o.OrderID) [RunningTotal]
   FROM 
      #orders o
   ORDER BY 
      o.OrderID;


Here is a little more information on the correlated subquery:
 http://technet.microsoft.com/en-us/library/ms187638(v=sql.105).aspx

And take a look at this for more detail on the OVER clause:
  http://msdn.microsoft.com/en-us/library/ms189461(v=sql.105).aspx