# 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

1,994

Solutions

Expert in:

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

### Paul Maxwell

9

Articles

2,670

Solutions

Expert in:

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

### Brian Crowe

1,564

Solutions

Expert in:

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

### ScottPletcher

8,553

Solutions

Expert in:

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

### lcohan

1,844

Solutions

Expert in:

• MS SQL Server
• MS SQL Server 2008
• MS SQL Server 2005
• SSRS
• Databases

### Jim Horn

17

Articles

10,429

Solutions

Expert in:

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

### Éric Moreau

2

Articles

10,316

Solutions

Expert in:

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

2

Solutions

### arnold

7,565

Solutions

Expert in:

• Linux
• Microsoft OS
• Linux Networking
• MS Server OS
• MySQL Server

## Related Questions

### RELATED TOPICS view all topics

1. MS SQL Server 2005
(71,495)
2. MS SQL Server 2008
(47,654)
3. Query Syntax
(45,823)
4. Databases
(52,646)
5. .NET Programming
(130,547)
6. MS Access
(213,346)
7. MS Development
(48,637)
8. ASP.NET
(121,704)
9. SSRS
(8,442)
10. Visual Basic.NET
(91,046)