RUNNING TOTAL FROM DETAIL DATA

emi_sastra
emi_sastra used Ask the Experts™
on
Hi All,

I have a transaction table :

T1 :

1. TrsId
2. TrsDate
3. Description
3. DebitAmt
4. CreditAmt

I want to query into report :

TrsDate   Debit  Credit  Balance
1                                              0
2             10             0             10
3              0               5             5

How could I do it ?

Thank you.





Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Declare @Balance as int = 0
select TrsId,TrsDate,DebitAmt,CreditAmt,((@Balance - DebitAmt)+ (@Balance+CreditAmt))as balance  from #T1

Assumed credited amount should be added in balance and debited amount should be deducted
(I am not good in accounts :) )

Hope you need the same. :)

Author

Commented:
Hi Cenjoy100,

I think your code is not running total ?

Thank you.
As I said I am not good in Account,Can you explain what do you mean by running total.

Or please check is following query works as you expect.

select t.TrsId,t.TrsDate,t.DebitAmt,t.CreditAmt,
(select sum((@Balance - DebitAmt)+ (@Balance+CreditAmt))  
      from #T1
      where TrsDate <=t.TrsDate) as running_total
from #T1 t

order by t.TrsDate

Thanks.
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Author

Commented:
Do you see the expected report?

Thank you.

Author

Commented:
Balance = Debit - Credit

Thank you.
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
SQL 2012 will make this very simple using the windowing functions, but you can accomplish this a couple ways in SQL 2005 -- performance is not great, but it can be done. One method is using a sub-query to get the previous balance by summing all previous debits minus all previous credits. I will mock up an example if needed. Note SQL MVP Jeremiah Peschka discusses another option using LEFT OUTER JOIN in his blog about the upcoming enhancements in SQL 2012 (see link above).
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Here is an example. In your report sample above you show an empty transaction row with beginning balance. If you want this, you could UNION that in or otherwise account for it; however, typically I would just have the first row be the first row of data as carryover balances should be a row that has a debit or credit amount that was rolled anyway.

/* sample data */
CREATE TABLE #account_ledger (
     TrsId INT IDENTITY(1,1)
   , TrsDate DATETIME NOT NULL DEFAULT(CURRENT_TIMESTAMP)
   , [Description] VARCHAR(100)
   , DebitAmt DECIMAL(19,4) NOT NULL DEFAULT(0)
   , CreditAmt DECIMAL(19,4) NOT NULL DEFAULT(0)
   , PRIMARY KEY (TrsId)
)
;

INSERT INTO #account_ledger([Description], DebitAmt, CreditAmt)
SELECT 'Test Entry', 10, 0
UNION ALL SELECT 'Test Entry', 0, 5
UNION ALL SELECT 'Test Entry', 12, 2
UNION ALL SELECT 'Test Entry', 0, 5
UNION ALL SELECT 'Test Entry', 10, 0
;
/* sample data */

/* example running total using sub-query */
SELECT TrsId
     , DebitAmt AS Debit
     , CreditAmt AS Credit
     , (SELECT SUM(b.DebitAmt-b.CreditAmt)
        FROM #account_ledger b
        WHERE b.TrsId <= a.TrsId) AS Balance
FROM #account_ledger a
;

/* cleanup */
DROP TABLE #account_ledger;

Open in new window

Author

Commented:
Hi mwvisa1,

If you see the tables and the result. You should aware that the Debit and Credit amount should use sum, since one date could have several TrsId.

The expected result should not have TrsId, please refer to the question.

Thank you.

Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
I read the question. You have a heading of TrsDate, but show values of 1, 2, 3, ... as if that was actually the TrsId. That minor fact does not change the solution much, but will depend on if your TrsDate is just the date or includes timestamps. Can you give a sample of the actual date values and the expected grouping.

Author

Commented:
I have a transaction table :

T1 :

1. TrsId
2. TrsDate
3. DebitAmt
4. CreditAmt

TrsId       TrsDate   Debit  Credit  
A             1             10             0                              
B             2             10             0            
C             3              0               5              
D             3              0               10              

I want to query into report :

TrsDate   Debit  Credit  Balance
1                                             0
1             10                           10
2             10             0             20
3              0              15            5

The first row is the beginning balance that get it from a constant data.

Thank you.
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Okay.

Using your sample data above:
/* sample data */
CREATE TABLE #account_ledger (
     TrsId CHAR(1)
   , TrsDate INT
   , DebitAmt DECIMAL(19,4) NOT NULL DEFAULT(0)
   , CreditAmt DECIMAL(19,4) NOT NULL DEFAULT(0)
   , PRIMARY KEY (TrsId)
)
;

INSERT INTO #account_ledger(TrsId, [TrsDate], DebitAmt, CreditAmt)
SELECT 'A', 1, 10, 0
UNION ALL SELECT 'B', 2, 10, 0
UNION ALL SELECT 'C', 3, 0, 5
UNION ALL SELECT 'D', 3, 0, 10
;
/* sample data */

Open in new window


The solution may look something like this:
/* example running total using sub-query */
SELECT TrsDate
     , SUM(DebitAmt) AS Debit
     , SUM(CreditAmt) AS Credit
     , (SELECT SUM(b.DebitAmt-b.CreditAmt)
        FROM #account_ledger b
        WHERE b.TrsDate <= a.TrsDate) AS Balance
FROM #account_ledger a
GROUP BY TrsDate
;

Open in new window


Hope that helps!

Author

Commented:
Great, how to insert the first date balance ?

TrsDate   Debit  Credit  Balance
1                                             0

The balance may any number other than 0.

Thank you.

Author

Commented:
Great, how to add the first date balance ?

TrsDate   Debit  Credit  Balance
1                                             0

The balance may any number other than 0.

Thank you.
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Where is it -- the beginning balance -- coming from?
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
One method would be to UNION in the value as in:

SELECT /* beginning balance row columns */
UNION
SELECT /* actual data rows/columns */

Since you indicated, "The balance may [be] any number other than 0," I get the impression that there is a specific input of the beginning balance; hence, my question above on where.  

Author

Commented:
From a constant.

Thank you.
Chief Technology Officer
Most Valuable Expert 2011
Commented:
"From a constant." < I am reading this as a variable; therefore, it would be like this:

/* example running total using sub-query */
DECLARE @BeginAmt DECIMAL(19,4);
SET @BeginAmt = 200.00;

SELECT 0 AS TrsDate
     , 0 AS Debit
     , 0 AS Credit
     , COALESCE(@BeginAmt, 0) AS Balance
UNION
SELECT TrsDate
     , SUM(DebitAmt) AS Debit
     , SUM(CreditAmt) AS Credit
     , (SELECT SUM(b.DebitAmt-b.CreditAmt)+COALESCE(@BeginAmt, 0)
        FROM #account_ledger b
        WHERE b.TrsDate <= a.TrsDate) AS Balance
FROM #account_ledger a
GROUP BY TrsDate
;

Open in new window


As you can see the principal is as I showed, using UNION. To get a true running balance, you have to ensure to add in the constant's value every time you SUM() the debits and credits.

Author

Commented:
Ok. Great.

Thank you very much for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial