Home » SQL & PL/SQL » SQL & PL/SQL » Sum of debit and Credit
Sum of debit and Credit [message #436858] Tue, 29 December 2009 01:10 Go to next message
manns
Messages: 59
Registered: March 2006
Location: Bangalore
Member

Account No Date Debit Credit
001-0001 8/2/2008 100 0
001-0001 8/2/2008 100 0
001-0001 8/3/2008 0 200
001-0001 8/4/2008 100 0
001-0001 8/5/2008 100 0
001-0001 8/8/2008 0 100
001-0002 8/2/2008 100 0
001-0002 8/15/2008 500 0




I want to calculate Balance that will be the sum of Debit - Credit + Balance(of Last row ) like this

Account No Date Debit Credit Balance
001-0001 8/2/2008 100 0 100
001-0001 8/2/2008 100 0 200
001-0001 8/3/2008 0 200 0
001-0001 8/4/2008 100 0 100
001-0001 8/5/2008 100 0 200
001-0001 8/8/2008 0 100 100
001-0002 8/2/2008 100 0 200
001-0002 8/15/2008 500 0 700




Here you can examine the result that want,

please guide me how can i do it.

Re: Sum of debit and Credit [message #436859 is a reply to message #436858] Tue, 29 December 2009 01:54 Go to previous messageGo to next message
cherry
Messages: 56
Registered: December 2007
Member
Next time pls bother to supply the create & insert statements like below.

CREATE TABLE T98
(
  ACCOUNT  VARCHAR2(10 BYTE),
  DDATE    DATE,
  DR       NUMBER,
  CR       NUMBER
)

Insert into T98
   (ACCOUNT, DDATE, DR, CR)
 Values
   ('001-0001', TO_DATE('08/02/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 100, 0);
Insert into T98
   (ACCOUNT, DDATE, DR, CR)
 Values
   ('001-0001', TO_DATE('08/02/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 100, 0);
Insert into T98
   (ACCOUNT, DDATE, DR, CR)
 Values
   ('001-0001', TO_DATE('08/03/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 0, 200);
Insert into T98
   (ACCOUNT, DDATE, DR, CR)
 Values
   ('001-0001', TO_DATE('08/04/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 100, 0);
Insert into T98
   (ACCOUNT, DDATE, DR, CR)
 Values
   ('001-0001', TO_DATE('08/05/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 100, 0);
Insert into T98
   (ACCOUNT, DDATE, DR, CR)
 Values
   ('001-0001', TO_DATE('08/08/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 0, 100);
Insert into T98
   (ACCOUNT, DDATE, DR, CR)
 Values
   ('001-0002', TO_DATE('08/02/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 100, 0);
Insert into T98
   (ACCOUNT, DDATE, DR, CR)
 Values
   ('001-0002', TO_DATE('08/02/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 100, 0);
Insert into T98
   (ACCOUNT, DDATE, DR, CR)
 Values
   ('001-0002', TO_DATE('08/15/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 500, 0);
COMMIT;

SELECT ACCOUNT, ddate, dr, cr, dr - cr balance,
       SUM (dr - cr) OVER (PARTITION BY NULL ORDER BY ROWNUM) cum_bal
  FROM t98


ACCOUNT	        DDATE	        DR	CR	BALANCE	CUM_BAL
001-0001	02/08/2008	100	0	100	100
001-0001	02/08/2008	100	0	100	200
001-0001	03/08/2008	0	200	-200	0
001-0001	04/08/2008	100	0	100	100
001-0001	05/08/2008	100	0	100	200
001-0001	08/08/2008	0	100	-100	100
001-0002	02/08/2008	100	0	100	200
001-0002	02/08/2008	100	0	100	300
001-0002	15/08/2008	500	0	500	800

[Updated on: Tue, 29 December 2009 01:54]

Report message to a moderator

Re: Sum of debit and Credit [message #436861 is a reply to message #436859] Tue, 29 December 2009 02:54 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Next time PLEASE do not use IM speak and do not post a full solution for homework but just a clue and/or a link to the function (above all when this has been so many times already answered).

Regards
Michel
Re: Sum of debit and Credit [message #436865 is a reply to message #436858] Tue, 29 December 2009 03:03 Go to previous messageGo to next message
manns
Messages: 59
Registered: March 2006
Location: Bangalore
Member

thanks. it worked.
Re: Sum of debit and Credit [message #436885 is a reply to message #436861] Tue, 29 December 2009 06:25 Go to previous messageGo to next message
cherry
Messages: 56
Registered: December 2007
Member
I prefer to analyse your advise & not blindly accept what you say. For me its simple: either provide the help requested, or get lost, because otherwise you're just wasting valuable volunteer time.
Re: Sum of debit and Credit [message #436888 is a reply to message #436885] Tue, 29 December 2009 06:37 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
./fa/1579/0/.
OP got the answer.Thats it.Dont post any thing irrelevant

sriram Smile
Re: Sum of debit and Credit [message #436890 is a reply to message #436888] Tue, 29 December 2009 07:05 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
ramoradba wrote on Tue, 29 December 2009 13:37
./fa/1579/0/.
OP got the answer.Thats it.Dont post any thing irrelevant

sriram Smile

It may be correct answer, as long as he is satisfied with the order Oracle fetches rows from the table.
SQL> with t98 as (
              select '001-0001' account, TO_DATE('08/02/2008', 'MM/DD/YYYY') ddate, 100 dr, 0 cr from dual
    union all select '001-0001', TO_DATE('08/02/2008', 'MM/DD/YYYY'), 100, 0 from dual
    union all select '001-0002', TO_DATE('08/02/2008', 'MM/DD/YYYY'), 100, 0 from dual
    union all select '001-0002', TO_DATE('08/02/2008', 'MM/DD/YYYY'), 100, 0 from dual
    union all select '001-0001', TO_DATE('08/03/2008', 'MM/DD/YYYY'), 0, 200 from dual
    union all select '001-0001', TO_DATE('08/04/2008', 'MM/DD/YYYY'), 100, 0 from dual
    union all select '001-0001', TO_DATE('08/05/2008', 'MM/DD/YYYY'), 100, 0 from dual
    union all select '001-0001', TO_DATE('08/08/2008', 'MM/DD/YYYY'), 0, 100 from dual
    union all select '001-0002', TO_DATE('08/15/2008', 'MM/DD/YYYY'), 500, 0 from dual)
     SELECT ACCOUNT, ddate, dr, cr, dr - cr balance,
            SUM (dr - cr) OVER (PARTITION BY NULL ORDER BY ROWNUM) cum_bal
       FROM t98;

ACCOUNT  DDATE             DR         CR    BALANCE    CUM_BAL
-------- --------- ---------- ---------- ---------- ----------
001-0001 02-AUG-08        100          0        100        100
001-0001 02-AUG-08        100          0        100        200
001-0002 02-AUG-08        100          0        100        300
001-0002 02-AUG-08        100          0        100        400
001-0001 03-AUG-08          0        200       -200        200
001-0001 04-AUG-08        100          0        100        300
001-0001 05-AUG-08        100          0        100        400
001-0001 08-AUG-08          0        100       -100        300
001-0002 15-AUG-08        500          0        500        800
As he did not describe the desired result in words, it is hard to tell. Anyway, wishing him good luck with eventual fixing the code without understanding it.
Re: Sum of debit and Credit [message #436896 is a reply to message #436890] Tue, 29 December 2009 07:16 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
manns

thanks. it worked.
.
I pointed On this.And more over My message was a reply for cherry`s answer...stated that to dont post any irrelavent (blaming other users).Hope it`s clear.

sriram Smile
Re: Sum of debit and Credit [message #436902 is a reply to message #436896] Tue, 29 December 2009 07:47 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
Ok, but if you read my answer carefully, then, although it "worked" for manns now, I do not think it will "work" every time.

I should rather address it to cherry:
I prefer to get exact requirements on the query & not blindly code something which may "work" only under specific circumstances. For me its simple: either provide answer with explanation (so the original poster will understand it) otherwise you're going to fix it for him every time source data "non-regularly" changes.
Re: Sum of debit and Credit [message #436909 is a reply to message #436885] Tue, 29 December 2009 08:01 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
cherry wrote on Tue, 29 December 2009 13:25
I prefer to analyse your advise & not blindly accept what you say. For me its simple: either provide the help requested, or get lost, because otherwise you're just wasting valuable volunteer time.

Do not analyze my advice and read OraFAQ Forum Guide, especially the first paragraph of "Responding to Posts" section to see the options that the forum have taken on this point.

Regards
Michel

Re: Sum of debit and Credit [message #436935 is a reply to message #436909] Tue, 29 December 2009 11:08 Go to previous messageGo to next message
cherry
Messages: 56
Registered: December 2007
Member
welcome to my IGNORE list Michelle. period.
Quote:

ramoradba

Dont post any thing irrelevant

sriram


Excuse me !!! My reply was to Michelle !! Hard to understand why people just cannot mind their business, instead of policing others.

flyboy: for the inputs provided by the user, this should work. if you understand the requirement of the user better, pls contribute. feel free to correct my query if it is wrong.

[Updated on: Tue, 29 December 2009 11:09]

Report message to a moderator

Re: Sum of debit and Credit [message #436937 is a reply to message #436935] Tue, 29 December 2009 11:15 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
welcome to my IGNORE list Michelle. period.

I don't care, just follow the guide.
By the way, my name is MICHEL, at least respect the name of people you talk to.

Quote:
Hard to understand why people just cannot mind their business, instead of policing others.

It is my moderator business to ask you to follow the guide.

Quote:
pls contribute

Don't use IM speak, read the guide and follow it.

Regards
Michel
Re: Sum of debit and Credit [message #436942 is a reply to message #436858] Tue, 29 December 2009 12:02 Go to previous messageGo to next message
Bill B
Messages: 1458
Registered: December 2004
Senior Member
Cherry,
You did a big disservice to manns. By helping them learn and just giving clues, they will learn the task. By being given the answer they learn nothing!!! I would not want someone working for me that doesn't know who to actually code. Keep up violating the rules and you will be the one banned. Your heart was in the right place but the user learned nothing.
Re: Sum of debit and Credit [message #436959 is a reply to message #436942] Tue, 29 December 2009 22:58 Go to previous messageGo to next message
cherry
Messages: 56
Registered: December 2007
Member
Glad that you have a perspective. I thought it is great if people come here & help others. And if you want to be considered helpful, you actually answer questions, which is another way to learn.

I appreciate flyboys comment which pointed out that the query will run only in some circumstances. I would not have known this, had I not replied.

This discussion has got little to do with the original post. So this will be my last post on the matter OR on the forum should you decide to ban me. I'll leave you to your loops of delusions...
Re: Sum of debit and Credit [message #436970 is a reply to message #436959] Wed, 30 December 2009 00:20 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
cherry
should you decide to ban me ...

For what? Answering the question? I think that you'd be the first one, then. If it, however, comes to that, I believe that I (me, LF) shall consider terminating my OraFAQ account.



I have no idea what's going on here lately; it is the end of the year, people should be easy-going, but there've been so much negative energy I've seen in a few threads that I can't believe it. What's the matter with you, people?
Re: Sum of debit and Credit [message #436983 is a reply to message #436970] Wed, 30 December 2009 03:13 Go to previous messageGo to next message
manns
Messages: 59
Registered: March 2006
Location: Bangalore
Member

hi,

I think i was posted my question, in such a way that which should understand every one. i said thanks to cherry for his answer, which worked for me.
hope i did not post any irrelevant. I dint aware that my question will trouble u all this much. I still feel that whoever know the answer can help the others since any one will not post the question blindly here. Once again thanks Cherry

rgds,
Manns
Re: Sum of debit and Credit [message #436986 is a reply to message #436983] Wed, 30 December 2009 03:28 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
which worked for me.

The point is not there, did you learn how to use analytic function with this answer?

Quote:
I still feel that whoever know the answer can help the others since any one will not post the question blindly here

This site choosed to help people learn how to find the solution by themselves rather than to give solution when the problem is not difficult.
This is explained in the OraFAQ Forum Guide, anyone that posts here should read and follow it.

Regards
Michel
Re: Sum of debit and Credit [message #436988 is a reply to message #436983] Wed, 30 December 2009 03:45 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
manns wrote on Wed, 30 December 2009 10:13
I think i was posted my question, in such a way that which should understand every one.

In that case, I am not "everyone" as it is ambiguous without explanation of the resultset in words.

In your example, data seem to be ordered by ACCOUNT, DDATE.
Cherry's query fetches them in non-specified order; they are fetched in that order just by chance. In my example, the same query fetches the same data in order of DDATE only. As you can see, resultsets of that query differ.

As data may be fetched in any order without explicit using ORDER BY clause, you might get ambiguous results. Will this be acceptable for you?

@cherry: I think you understand why your and my query differ. It would be nice if you at least described, how CUM_BAL is counted.
Re: Sum of debit and Credit [message #436989 is a reply to message #436988] Wed, 30 December 2009 03:54 Go to previous messageGo to next message
manns
Messages: 59
Registered: March 2006
Location: Bangalore
Member

Hi Michel,

Definitely. I learnt from this answer. This was new to me.

Flyboy,
I just wanted to calculate Balance that will be the sum of Debit - Credit + Balance(of Last row ). I did not know how to calculate the cumulative balance.
I wasn't concern about order.

Rgds,
Manns
Re: Sum of debit and Credit [message #436990 is a reply to message #436989] Wed, 30 December 2009 04:00 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I wasn't concern about order.

You are just to know what is "of Last row", also what is the meaning of the balance if you start with "8/2/2008" then go to "8/15/2008" then to "8/4/2008" and so on in a random order?

This is why ORDER BY clause is mandatory in such analytic function.
So "I learnt from this answer" is not so much if you didn't understand the importance of the order.
I'm sorry but I doubt you will be able to use analytic function in another question.

Regards
Michel
Re: Sum of debit and Credit [message #436991 is a reply to message #436990] Wed, 30 December 2009 04:16 Go to previous message
manns
Messages: 59
Registered: March 2006
Location: Bangalore
Member

Sorry Michel,

I wanted to say that I learnt something from the answer. i.e. How to use this analytical function(I am not saying I am master of analytical function )
Anyway it gave me timely help. Thanks.

Rgds,
Manju
Previous Topic: masking card number first 10 ten digits (merged)
Next Topic: need procedure
Goto Forum:
  


Current Time: Wed Sep 28 07:24:27 CDT 2016

Total time taken to generate the page: 0.15177 seconds