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:
    

    Select allOpen in new window

    Top Expert Contributor

    Essential articles and videos from the Experts

    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

    bcnagel

    25

    Solutions

    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)