# SQL Server 2000 ; have 10 rows of DEBIT + CREDIT columns, can SQL tally up the balances for each account?

Question by:
On

## Topics:

hi, this is my table. is it possible do work out the balance via SQL, im currently doing it in .net.
Account   Date                         Debit        Credit      Balance
10139      2007-08-31       2025.91      0.0      0.0
10139      2007-08-31       0.0      3620.11      0.0
10139      2007-09-30       4631.52      0.0      0.0
10139      2007-09-30       0.0      11336.71      0.0
10139      2007-09-30       0.0      14.8801      0.0
12211      2007-08-31       1352.76      0.0      0.0
12211      2007-08-31       0.0      3872.5      0.0

e.g. will be
Account   Date                         Debit        Credit      Balance
10139      2007-08-31       205      0.0      -205
10139      2007-08-31       0.0      305      +100
10139      2007-09-30       0.0      15      +115
12211      2007-08-31       135      0.0      -135
12211      2007-08-31       0.0      325      +190

Good Question?
0

The member who asked this question verified this comment provided the solution that solved their problem.

Accepted Solution on 2007-11-30 at 04:51:06ID: 20381365

This should do it

``````CREATE TABLE
#mutations
(
Account INTEGER,
Date DATETIME,
Debit DECIMAL(9,2),
Credit DECIMAL(9,2)
)

INSERT INTO #mutations VALUES (10139, '2007-08-31', 2025.91, 0.0)
INSERT INTO #mutations VALUES (10139, '2007-08-31', 0.0, 3620.11)
INSERT INTO #mutations VALUES (10139, '2007-09-30', 4631.52, 0.0)
INSERT INTO #mutations VALUES (10139, '2007-09-30', 0.0, 11336.71)
INSERT INTO #mutations VALUES (10139, '2007-09-30', 0.0, 14.8801)
INSERT INTO #mutations VALUES (12211, '2007-08-31', 1352.76, 0.0)
INSERT INTO #mutations VALUES (12211, '2007-08-31', 0.0, 3872.5)

/*
e.g. will be
Account   Date                         Debit        Credit      Balance
10139      2007-08-31       205      0.0      -205
10139      2007-08-31       0.0      305      +100
10139      2007-09-30       0.0      15      +115
12211      2007-08-31       135      0.0      -135
12211      2007-08-31       0.0      325      +190
*/

SELECT
account,
date,
SUM(Debit) AS Debit,
- SUM(Credit) AS Credit,
(SELECT ISNULL(SUM(m2.Debit),0) - ISNULL(SUM(m2.Credit),0) FROM #mutations m2 WHERE m1.account = m2.account AND m2.date <= m1.date AND

CASE WHEN m2.debit <> 0 THEN 0 ELSE 1 END <= CASE WHEN m1.debit <> 0 THEN 0 ELSE 1 END

) AS Balance
FROM
#mutations m1
GROUP BY
account,
date,
CASE WHEN debit <> 0 THEN 0 ELSE 1 END
ORDER BY
account,
date,
CASE WHEN debit <> 0 THEN 0 ELSE 1 END

DROP TABLE #mutations```
```
```1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
```

## More valuable questions with Expert answers

201511-LO-Qu-074

## Extend your technology team with the Experts Exchange community.

— trusted by —

## Who answers my questions?Our community has technology experts around the world.

### Vitor Montalvão

11

Articles

2,148

Solutions

Expert in:

• MS SQL Server
• MS SQL Server 2008
• MS SQL Server 2005
• Query Syntax
• Databases

### Paul Maxwell

9

Articles

2,784

Solutions

Expert in:

• MS SQL Server
• MS SQL Server 2008
• Query Syntax
• MS SQL Server 2005
• Oracle Database

### Jim Horn

18

Articles

10,528

Solutions

Expert in:

• MS Access
• MS SQL Server
• MS SQL Server 2008
• MS SQL Server 2005
• Query Syntax

### Brian Crowe

1,655

Solutions

Expert in:

• MS SQL Server
• MS SQL Server 2008
• Visual Basic.NET
• Query Syntax
• Crystal Reports

### geek_vj

305

Solutions

Expert in:

• MS SQL Server
• MS SQL Server 2005
• MS SQL Server 2008

### ScottPletcher

8,624

Solutions

Expert in:

• MS SQL Server
• MS SQL Server 2008
• MS SQL Server 2005
• Query Syntax
• Databases

### Zberteoc

905

Solutions

Expert in:

• MS SQL Server
• MS SQL Server 2005
• MS SQL Server 2008
• Query Syntax
• Databases

### Éric Moreau

2

Articles

10,368

Solutions

Expert in:

• .NET Programming
• Visual Basic.NET
• C#
• Visual Basic Classic
• ASP.NET

25

Solutions

## Related Questions

### RELATED TOPICS view all topics

1. MS SQL Server 2005
(71,594)
2. MS SQL Server 2008
(48,082)
3. Query Syntax
(46,086)
4. Databases
(53,008)
5. .NET Programming
(131,110)
6. MS Access
(214,030)
7. MS Development
(48,691)
8. ASP.NET
(122,103)
9. SSRS
(8,517)
10. Visual Basic.NET
(91,439)