SQL server 2008 -账户余额的SQL运行总数

Question

我正试图在SQL2008中准备一个明细分类账视图报告。下面是临时表

AccountCode |Date       |Fiche Type      |Debit    |Credit  |Balance
100.001.001 |1.1.2015   |Açılış Fişi     |1856,09  |0       |1856,09
100.001.002 |1.1.2015   |Mahsup Fişi     |0        |20      |-20
100.001.001 |1.2.2015   |Tediye Fişi     |0        |950     |906,09
100.001.002 |1.2.2015   |Açılış Fişi     |2931,37  |0       |2911,37
100.001.001 |1.4.2015   |Mahsup Fişi     |0        |6,25    |899,84
100.001.002 |1.6.2015   |Tahsil Fişi     |0        |400     |2511,37
100.002.001 |1.7.2015   |Açılış Fişi     |0        |1969,5  |-1969,5
100.002.001 |1.20.2015  |Mahsup Fişi     |217,6    |0       |-1751,9
100.001.001 |1.21.2015  |Mahsup Fişi     |500      |0       |1399,84
100.002.001 |1.21.2015  |Tediye Fişi     |2000     |0       |248,1
100.001.002 |1.21.2015  |Tahsil Fişi     |543,34   |0       |3054,7

在最后一列中，我试图计算第一列中accountcode的余额。余额字段的公式很简单(借方-贷方)。报告应按日期排序。因此，例如在第三行中，由于帐户代码是相同的，因此余额字段必须采用第一列的值并将它们与第三列的值(Row1(Debit-Credit)+Row3(Debit-Credit))组合在一起。

我尝试了很多东西，但都没有运气，因为列表不是按照运行总数所基于的标准排序的。

谁能给我一个解决方案?

Answer 1

    with x as 
    (
      select *,row_number() over (partition by accountcode order by date) sr 
      from ledger
    )
    select 
      (select sum(debit-credit) from x y where y.accountcode=x.accountcode and y.sr<x.sr) opening
      ,*
      ,(select sum(debit-credit) from x y where y.accountcode=x.accountcode and y.sr<=x.sr) closing
    from x

见SQL Fiddle这里

HTH

Answer 2

在SQL Server 2012+中，可以使用累加和。在SQL Server 2008中，您可以使用关联子查询、连接或应用。下面是后者:

select l.*, cume.balance
from ledger l cross apply
     (select sum(debit - credit) as balance
      from ledger l2
      where l2.AccountCode = l.AccountCode and
            l2.date <= l.date
     ) cume;