Performant way to get the maximum value of a running total in TSQL

Question

We have a table of transactions which is structured like the following :

TranxID    int (PK and Identity field)
ItemID     int
TranxDate  datetime
TranxAmt   money

TranxAmt can be positive or negative, so the running total of this field (for any ItemID) will go up and down as time goes by. Getting the current total is obviously simple, but what I'm after is a performant way of getting the highest value of the running total and the TranxDate when this occurred. Note that TranxDate is not unique, and due to some backdating the ID field is not necessarily in the same sequence as TranxDate for a given Item.
Currently we're doing something like this (@tblTranx is a table variable containing just the transactions for a given Item) :

SELECT Top 1 @HighestTotal = z.TotalToDate, @DateHighest = z.TranxDate
FROM
    (SELECT a.TranxDate, a.TranxID, Sum(b.TranxAmt) AS TotalToDate
    FROM @tblTranx AS a
    INNER JOIN @tblTranx AS b ON a.TranxDate >= b.TranxDate
    GROUP BY a.TranxDate, a.TranxID) AS z
ORDER BY z.TotalToDate DESC

(The TranxID grouping removes the issue caused by duplicate date values)

This, for one Item, gives us the HighestTotal and the TranxDate when this occurred. Rather than run this on the fly for tens of thousands of entries, we only calculate this value when the app updates the relevant entry and record the value in another table for use in reporting.

The question is, can this be done in a better way so that we can work out these values on the fly (for multiple items at once) without falling into the RBAR trap (some ItemIDs have hundreds of entries). If so, could this then be adapted to get the highest values of subsets of transactions (based on a TransactionTypeID not included above). I'm currently doing this with SQL Server 2000, but SQL Server 2008 will be taking over soon here so any SQL Server tricks can be used.

Answer 1

SQL Server sucks in calculating running totals.

Here's a solution for your very query (which groups by dates):

WITH    q AS
        (
        SELECT  TranxDate, SUM(TranxAmt) AS TranxSum
        FROM    t_transaction
        GROUP BY
                TranxDate
        ),
        m (TranxDate, TranxSum) AS
        (
        SELECT  MIN(TranxDate), SUM(TranxAmt)
        FROM    (
                SELECT  TOP 1 WITH TIES *
                FROM    t_transaction
                ORDER BY
                        TranxDate
                ) q
        UNION ALL
        SELECT  DATEADD(day, 1, m.TranxDate),
                m.TranxSum + q.TranxSum
        FROM    m
        CROSS APPLY
                (
                SELECT  TranxSum
                FROM    q
                WHERE   q.TranxDate = DATEADD(day, 1, m.TranxDate) 
                ) q
        WHERE   m.TranxDate <= GETDATE()
        )
SELECT  TOP 1 *
FROM    m
ORDER BY
        TranxSum DESC
OPTION (MAXRECURSION 0)

You need to have an index on TranxDate for this to work fast.