Running totals in a SQL view

I am trying to get running totals in my View in SQL Server 2008

Here is my tables

BankAccounts
------------
AccountID (KEY)
Name
Created

Transactions
------------
TransactionID (KEY)
Description
Credit
Debit
TransDate
Created
AccountID

Here is my query so far..

SELECT t.Created, t.Description, t.Credit, t.Debit, t.TransDate, t.TransactionID, ba.AccountID,
        (isnull(t.Credit,0)-isnull(t.Debit,0))+COALESCE((SELECT SUM(isnull(Credit,0)) - SUM(isnull(Debit,0))
                              FROM Transactions b 
                              WHERE b.TransDate < t.TransDate
                               and b.AccountID = t.AccountID),0)
                                 AS RunningTotal
FROM  Transactions t 
INNER JOIN dbo.BankAccounts ba ON t.AccountID = ba.AccountID

What I'm getting is..

TransDate               Credit                 Debit                  RunningTotal
----------------------- ---------------------- ---------------------- ---------------------
2011-10-08 20:14:00     NULL                   12                     开发者_如何学Go49.25
2011-10-08 20:14:00     2.11                   NULL                   63.36
2011-10-07 20:14:00     42.25                  NULL                   61.25
2011-10-06 20:14:00     NULL                   12.25                  19
2011-10-05 20:14:00     31.25                  NULL                   31.25

What it should look like...

TransDate               Credit                 Debit                  Running Total
----------------------- ---------------------- ---------------------- ---------------------
2011-10-08 00:31:32.957 NULL                   12                     51.36
2011-10-08 00:31:32.957 2.11                   NULL                   63.36
2011-10-07 00:31:32.957 42.25                  NULL                   61.25
2011-10-06 00:31:32.957 NULL                   12.25                  19
2011-10-05 00:31:32.960 31.25                  NULL                   31.25

I'm really close.. just seems when there are 2 transactions for same day, it doesn't calculate it correctly.. any ideas?

I used ROW_NUMBER AND a CTE since you're in 2008

WITH transactionTotal AS
(
   SELECT t.Created, t.Description, t.Credit, t.Debit, t.TransDate, t.TransactionID, a.AccountID
      , ROW_NUMBER() OVER (ORDER BY TransDate ASC) AS RowNumber
      , ( ISNULL(t.Credit, 0) - ISNULL(t.Debit, 0) ) AS TransactionTotal
   FROM dbo.Transactions AS t 
   INNER JOIN dbo.BankAccounts AS a ON t.AccountID = a.AccountID
)
SELECT t.Created, t.Description, t.Credit, t.Debit, t.TransDate, t.TransactionID, t.AccountID
   , ( SELECT SUM(tt.TransactionTotal)
       FROM transactionTotal AS tt
       WHERE tt.RowNumber <= t.RowNumber) AS RunningTotal
FROM transactionTotal AS t
LEFT JOIN transactionTotal AS tt ON t.RowNumber = tt.RowNumber + 1
ORDER BY t.TransDate DESC

SELECT t.Created, t.Description, t.Credit, t.Debit, t.TransDate, t.TransactionID, ba.AccountID,
       coalesce((select sum(ISNULL(Credit,0) - ISNULL(Debit, 0)) 
                 from Transactions
                 where TransactionID <= t.TransactionID and 
                       AccountID = ba.AccountID and
                      convert(date, TransDate) = convert(date, t.TransDate)),0)
        AS [Running Total]

FROM  Transactions t INNER JOIN
         dbo.BankAccounts ba ON t.AccountID = ba.AccountID

--I would use the existing identity column to be 100% sure that I am dealing with the correct transaction.

SELECT t.Created, t.Description, t.Credit, t.Debit, t.TransDate, t.TransactionID, ba.AccountID,
        (isnull(t.Credit,0)-isnull(t.Debit,0))+COALESCE((SELECT SUM(isnull(Credit,0)) - SUM(isnull(Debit,0))
                              FROM Transactions b 
                              WHERE b.TransactionID < t.TransactionID 
                               and b.AccountID = t.AccountID),0)
                                 AS RunningTotal
FROM  Transactions t 
INNER JOIN dbo.BankAccounts ba ON t.AccountID = ba.AccountID

--also if you change the "Less Than" to "Less Than or Equal To", then you don't have to add the current item:

SELECT t.Created, t.Description, t.Credit, t.Debit, t.TransDate, t.TransactionID, ba.AccountID,
COALESCE((SELECT SUM(isnull(Credit,0)) - SUM(isnull(Debit,0))
                              FROM Transactions b 
                              WHERE b.TransactionID <= t.TransactionID  
                               and b.AccountID = t.AccountID),0)
                                 AS RunningTotal
FROM  Transactions t 
INNER JOIN dbo.BankAccounts ba ON t.AccountID = ba.AccountID

Totals should be : (Assuming) Starting Balance: 49.25

TransDate               Credit            Debit          RunningTotal      
----------------------- ----------------- -------------- ----------------- 
2011-10-08 20:14:00     NULL              12             37.25 
2011-10-08 20:14:00     2.11              NULL           39.36            
2011-10-07 20:14:00     42.25             NULL           81.61
2011-10-06 20:14:00     NULL              12.25          69.36
2011-10-05 20:14:00     31.25             NULL           100.61

if this is Oracle, then there are Window functions. you can use LEAD and/or LAG to perform calculations on the current row with respect to prior or upcoming rows (based on sort order)

I'm trying to use this logic with RowNumber and CTE from above. In my scenario, I need the Running Total to be calculated for a combination of two fields: SalesProdLineID and FiscYerPer. Here's what I have coded (in this example, due to the size of the underlying tables I restricted the results to a single Month:

 WITH RunningTotal AS
(
   SELECT to2PN.CompanyID, REPLACE(SP.SalesProdLineID, ' Sls PL','') AS SlsPL, vo2PNQtyProd.QtyProdStock,  FP.FiscYearPer, FP.FiscPer, FP.FiscYear
      , ROW_NUMBER() OVER (ORDER BY SP.SalesProdLineID,FP.FiscYearPer  ASC) AS RowNumber
      , ( ISNULL(vo2PNQtyProd.QtyProdStock, 0)  ) AS RunningTotal
   FROM to2PN  (NOLOCK)
 JOIN to2PNProdTempl   (NOLOCK)
     ON to2PN.PNKey = to2PNProdTempl.PNKey
JOIN timItem I  (NOLOCK)
    ON to2PNProdTempl.ItemKey = I.ItemKey
 JOIN timSalesProdLine SP (NOLOCK)
    ON I.SalesProdLineKey = SP.SalesProdLineKey
 JOIN vo2PNQtyProd   (NOLOCK)
     ON to2PNProdTempl.PNProdTemplKey=vo2PNQtyProd.PNProdTemplKey
 JOIN tglFiscalPeriod FP (NOLOCK)
    ON I.CompanyID = FP.CompanyID
        AND to2PN.ComplDateTime BETWEEN FP.StartDate AND Fp.EndDate
WHERE I.ItemID BETWEEN '0000-0' AND '1999-9'
    AND YEAR(to2PN.[ComplDateTime]) = '2018'    --  !! COMMENT OUT for final
    AND MONTH(to2PN.[ComplDateTime]) = 5    --  !! COMMENT OUT for final
)
SELECT t.CompanyID, t.SlsPL, t.QtyProdStock, t.FiscYearPer, t.FiscPer, t.FiscYear
   , ( SELECT SUM(tt.RunningTotal)
       FROM RunningTotal AS tt
       WHERE tt.RowNumber <= t.RowNumber) AS RunningTotal
FROM RunningTotal AS t
LEFT JOIN RunningTotal AS tt ON t.RowNumber = tt.RowNumber + 1
ORDER BY t.FiscYearPer DESC

The issue is that once it's got the correct total for the first SalesProdLineID, it simply adds that Running total to the next SalesProdLineID.