Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2012
    Posts
    1

    Unanswered: Running Sum In SQL

    Hi, I am working on a software primarily related to accounting with visual basic

    Many of the problems and proposed several solutions for running sum, but I have read almost all of these interpretations,

    have applied when running sum is a unique ID field. but almost all of the functioning of accounting reports are in chronological order. So he questioned what was the balance on 05.05.2012.

    ID of the process is done, if the user enters a process backward a few things since then, the transaction date is old, but the ID is new, are experiencing problems. eg

    ID DATE DEBIT CREDIT BALANCE

    1 02.02.2012 100.00 0.00 100.00

    6 04.04.2012 0.00 150.00 -50.00

    3 02.05.2012 70.00 0.00 20.00

    4 02.05.2012 80.00 0.00 100.00

    2 06.06.2012 120.00 0.00 220.00

    must balance the figure above.

    if ID = 2 record, the record date 06/06/2012 and if the ID = 6, date 04/04/2012, the date the order is made if the listing is experiencing trouble getting balance. 2 records in the same day when the balance at worst formula breaks down. The big point is that I hang out in my project.

    the only solution is to use a temporary table or writing about it in Visual Basic seems to calculate. the use of temporary table is not a solution, but unfortunately also very fast.

    Sorry for my english. I use Google Translate
    Thanks in advance,


    Regards,

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE @t TABLE (
       [ID]     INT
    ,  [DATE]   DATE
    ,  [DEBIT]  MONEY
    ,  [CREDIT] MONEY
       )
    
    INSERT INTO @t VALUES
      (1, '2012-02-02', 100.00,   0.00)
    , (2, '2012-06-06', 120.00,   0.00)
    , (3, '2012-05-02',  70.00,   0.00)
    , (4, '2012-05-02',  80.00,   0.00)
    , (6, '2012-04-04',   0.00, 150.00)
    
    SELECT [ID], [DATE], [DEBIT], [CREDIT]
    ,  Sum([DEBIT] - [CREDIT]) OVER (ORDER BY [DATE], [ID]) AS [BALANCE]
       FROM @t
    Don't worry about your English offending anyone at DBForums. Be aware that the Google Translate could confuse us, but the translation won't offend us as long as you make an effort to be understood and to help solve your own problems!

    Your problem is a great example of using OVER Clause (Transact-SQL) to solve a running balance problem.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •