CODEWITHSUNDEEP
sqlsql-server-2008cumulative-sum
Orkun Antmen
Orkun Antmen

Reputation: 19

sql running total for an account balance

I am trying to prepare a subsidiary ledger view report in SQL2008. Here is the temp table

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

In the last column I am trying to calculate the balance for the accountcode in the first column. The formula for balance field is simply (Debit-Credit). Report should order by date. So for example in the 3rd row, since the account codes are the same, the balance field must take the first columns values and combine them with the 3rd columns' values (Row1(Debit-Credit)+Row3(Debit-Credit)).

I tried many things but no luck since the list is not ordered by the criteria which the running total based on.

Can anyone offer me a solution?

Upvotes: 1

Views: 1380

Answers (2)

user504466
user504466

Reputation: 

    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

see on SQL Fiddle here

HTH

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

In SQL Server 2012+, you can use cumulative sum for this. In SQL Server 2008, you can use a correlated subquery, join, or apply. Here is the latter:

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;

Upvotes: 0

Related Questions