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

     

    Verified Answer?

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

    by:Posted 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

    This content is available to Experts Exchange members

    See the answer now
    with your Free 30 Day Trial

    Get unlimited access to solutions & experts

    • 4,169,477 solved questions
    • 3,805 articles & videos
    • 15,413 tech experts

    Get Access Now

    Ask Your Tech Question. Get Expert Solutions.We will email you when an expert has commented on your question.

    We will never share this with anyone. Privacy Policy Terms of Use

    Select topics

    You may select up to five topics.

    Top Expert Contributor

    Essential articles and videos from the Experts

    More valuable questions with Expert answers

    201507-LO-Qu-065

    RELATED TOPICS view all topics

    1. MS SQL Server 2005
      (71,299)
    2. MS SQL Server 2008
      (46,888)
    3. Query Syntax
      (45,480)
    4. Databases
      (52,085)
    5. .NET Programming
      (129,698)
    6. MS Access
      (212,541)
    7. MS Development
      (48,485)
    8. ASP.NET
      (121,151)
    9. SSRS
      (8,308)
    10. Visual Basic.NET
      (90,470)