SQL Running Balance Query - Please Help

SQL Running Balance Query - Please Help

Post by Patrick Kimbe » Wed, 11 Nov 1998 04:00:00



Is it possible for an SQL statement to generate a running total.

The current query is similar to this:

SELECT TRANDATE, REF, DEBIT, CREDIT FROM ACCOUNTS WHERE LEDGER = 'S'

This query would typically return hundreds of records.  Is it possible for
debit and credit to be totalled to create a running balance as follows...

TranDate Ref Debit Credit Balance
12/02/1998 12 100.00   100.00
13/02/1998 13   50.00 50.00
14/02/1998 14 25.00   75.00
etc...

Please help...
Patrick

 
 
 

SQL Running Balance Query - Please Help

Post by Linda Anders » Wed, 11 Nov 1998 04:00:00


Quote:>Is it possible for an SQL statement to generate a running total.

>The current query is similar to this:

>SELECT TRANDATE, REF, DEBIT, CREDIT FROM ACCOUNTS WHERE LEDGER = 'S'

>This query would typically return hundreds of records.  Is it possible for
>debit and credit to be totalled to create a running balance as follows...

>TranDate Ref Debit Credit Balance
>12/02/1998 12 100.00   100.00
>13/02/1998 13   50.00 50.00
>14/02/1998 14 25.00   75.00
>etc...

The following query will return rows exactly as your example.
:
  SELECT TRANDATE, REF, SUM(DEBIT), SUM(CREDIT) FROM ACCOUNTS
     WHERE LEDGER = 'S'  GROUP BY TRANDATE, REF
In this case, it helps if you have an index defined on TRANDATE + REF.

There is no query that will return both the detail lines (as described
by your query) and the subtotals.
Linda Anderson
Customer Engineering - Pervasive Software

 
 
 

1. Stuck on Query for Running Invoice Balance and Payments

Hi,

I'm stuck on an accounting query. This seems like a common query for anyone doing accounting systems, so, hopefully someone can help!

I have two tables with the following fields:

INVOICES
inv_date
inv_number
inv_amount

PAYMENTS
pay_date
inv_number
pay_amount

The tables are linked by the field, inv_number

What I need is to create a query that has a "running balance" column for a certain period of time.

For example, if the invoice is for $100, and the customer makes 5 separate payments:
1. 01/01/2002 for $20
2. 02/01/2002 for $50
3. 02/01/2002 for $17
4. 02/15/2002 for $8
5. 03/01/2002 for $5

...and I want to generate a query for the month of February 2002 (02/01/2002 - 02/28/2002), then this is what I want to have output:

pay_date    prev_balance  pay_amount  new_balance
----------  ------------  ----------  -----------
02/01/2002  80.00         50.00       30.00
02/01/2002  30.00         17.00       13.00
02/15/2002  13.00          8.00        5.00

As you can see there was a previous balance before 02/01/2002 where payment(s) had been applied which is why the starting prev_balance is 80.00. Also, on 02/01/2002 there were two payments applied on the same day.

So, I'm stuck with how to write a query to get the output like it's shown above.

I would really appreciate your help.

Thanks,

Scott

2. How to ...

3. Running Balance in a query

4. SLOW datafile read problem

5. APPROACH QUERY-PLEASE PLEASE PLEASE PLEASE HELP

6. Foro de D3 en Castellano

7. SQL Puzzle - Applying calculations to running balances in a specified order

8. newbie question

9. Running Balances & SQL

10. APPROACH QUERY-PLEASE PLEASE PLEASE HELP

11. Help with running an SQL query in MS-SQL Server 6.5

12. COMBO BOX, PLEASE HELP, PLEASE HELP, PLEASE HELP!

13. Automatic SQL Server Restart caused job not to run--PLEASE HELP