RUNNING TOTAL FROM DETAIL DATA

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.





LVL 1
emi_sastraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Cenjoy100Commented:
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. :)
0
emi_sastraAuthor Commented:
Hi Cenjoy100,

I think your code is not running total ?

Thank you.
0
Cenjoy100Commented:
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.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

emi_sastraAuthor Commented:
Do you see the expected report?

Thank you.
0
emi_sastraAuthor Commented:
Balance = Debit - Credit

Thank you.
0
Kevin CrossChief Technology OfficerCommented:
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).
0
Kevin CrossChief Technology OfficerCommented:
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

0
emi_sastraAuthor 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.

0
Kevin CrossChief Technology OfficerCommented:
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.
0
emi_sastraAuthor 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.
0
Kevin CrossChief Technology OfficerCommented:
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!
0
emi_sastraAuthor 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.
0
emi_sastraAuthor 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.
0
Kevin CrossChief Technology OfficerCommented:
Where is it -- the beginning balance -- coming from?
0
Kevin CrossChief Technology OfficerCommented:
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.  
0
emi_sastraAuthor Commented:
From a constant.

Thank you.
0
Kevin CrossChief Technology OfficerCommented:
"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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
emi_sastraAuthor Commented:
Ok. Great.

Thank you very much for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.