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

Text after "

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

  TranxID integer ( P and Identity Area) ItemID Integer TranxDate datetime TranxAmt Money  

TranxAmt can be positive or negative, hence the running total of this field (for any ItemID) as above and below time Will also be known by going to. Getting the current total is clearly simple, but what I am doing is a great way to get the maximum value of the total value running and this happened when TranxDate happened. Note that TranxDate is not unique, and due to backdating some ID fields, it is not necessary in the same order as a TranxDate of a particular item.
We are currently doing something like this (@tblTranx is a table containing only variables containing a particular item's transaction):

  select top 1 @HighestTotal = z. Join TotalToDate, @DateHighest = z.TranxDate (Selection a.TranxDate, a.TranxID, as an add-on (b.TranxAmt) as an @TblTranx as @TblTranx from TotalToDate as aTranxDate & gt; In the form of Z. Order  

(The Transaxed Grouping Issue Due to Duplicate Debt Value, in the;; b.TranxDate Group a.TranxDate, a.TranxID) by Z ShortToday DESC O Removes

This gives us the highest and TranxDate, for one item, when it happened. Rather than running on the fly for thousands of entries, we only calculate this value when the app updates the relevant entry and records the value in another table for use in reporting.

The question is, this is done in a better way so that these values ​​can work simultaneously when flying without RBAR trap (some item IDID has hundreds of entries). If so, then it can then be optimized to achieve the highest values ​​of the transaction subset (depending on the type of transaction involved in the above) I am currently doing this with SQL Server 2000, but SQL Server 2008 will be here shortly so that any SQL Server tricks can be used.

SQL server is useless in computing the running sum

< P> Here is a solution for your very query (groups by date).

<> with code pre q (Select TranxDate, SUM (TranxAmt) as tranxSum by TranxDate from the t_transaction group), as in TranxDate (TranxDate), (SUM (TranxDate) TranxDate by TranxAmt (Select Top 1 relationship with * TranxDate by) X Union All choose DATEADD (day, 1, m.TranxDate) from the t_transaction order, from m.TranxSum + q.TranxSum meter cross (selected from TranxSum Q Where q.TranxDate = DATEADD (day, 1, m.TranxDate)) Where is m.TranxDate & lt; = GETDATE ()) Select Top 1 * m order by TranxSum DESC option (MAXRECURSION 0) < / Pre>

You need TranxDate this To work with English.

for an index