在SQL视图中运行总计 [英] Running totals in a SQL view

问题描述

我正在尝试在SQL Server 2008的视图中获取运行总计

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

这是我的表

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

我得到的是..

TransDate               Credit                 Debit                  RunningTotal
----------------------- ---------------------- ---------------------- ---------------------
2011-10-08 20:14:00     NULL                   12                     49.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

我真的很近..似乎当有当天有2笔交易，它计算不正确..有任何想法吗？

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

推荐答案

我使用了 ROW_NUMBER 和 CTE ，因为您在2008年

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

这篇关于在SQL视图中运行总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！