Home » SQL & PL/SQL » SQL & PL/SQL » running total (9.2, Linux)
running total [message #433972] Sun, 06 December 2009 01:46 Go to next message
cherry
Messages: 56
Registered: December 2007
Member
hi all
this is a simplified version of what im trying to achieve. the query runs into pages & i have the output from it, which i've used to populate data for you.

CREATE TABLE t97
(customer_number  INTEGER,
customer_name  VARCHAR2(100),
days  INTEGER,
payment_schedule_id  INTEGER,
CLASS VARCHAR2(3),
amt   INTEGER,
applied_payment_schedule_id INTEGER,
amount_applied INTEGER,
final_outstanding INTEGER)
/

insert into t97 values (1135,'ABC',775,4431,'PMT',-466,4431,160,-306);
insert into t97 values (1135,'ABC',791,4431,'PMT',-466,4431,306,-160);
insert into t97 values (1135,'ABC',361,4433,'PMT',-150,4433,100,-50);
insert into t97 values (1135,'ABC',50,4444,'PMT',-150,4444,100,-50);
insert into t97 values (1135,'ABC',56,4444,'PMT',-150,4444,25,-125);
insert into t97 values (1135,'ABC',61,4444,'PMT',-150,4444,25,-125);
insert into t97 values (1135,'ABC',91,5555,'PMT',-500,5555,200,-200);
insert into t97 values (1135,'ABC',182,5555,'PMT',-500,5555,200,-200);

cno	cname	DAYS	sch_id	CLASS	AMT	asch_id	APPLIED	outstanding
1135	ABC	182	5555	PMT	-500	5555	200	-200
1135	ABC	91	5555	PMT	-500	5555	200	-200
1135	ABC	61	4444	PMT	-150	4444	25	-125
1135	ABC	56	4444	PMT	-150	4444	25	-125
1135	ABC	50	4444	PMT	-150	4444	100	-50
1135	ABC	361	4433	PMT	-150	4433	100	-50
1135	ABC	791	4431	PMT	-466	4431	306	-160
1135	ABC	775	4431	PMT	-466	4431	160	-306


have a look at the values in "payment_schedule_id" column. i want to group the data based on this column. my output should resemble as:

cno	cname	DAYS	sch_id	CLASS	AMT	asch_id	APPLIED	outstanding
1135	ABC	182	5555	PMT	-200	5555	200	0
1135	ABC	91	5555	PMT	-200	5555	200	0
1135	ABC	91	5555	PMT	-100	5555		-100
1135	ABC	61	4444	PMT	-25	4444	25	0
1135	ABC	56	4444	PMT	-25	4444	25	0
1135	ABC	50	4444	PMT	-100	4444	100	0
1135	ABC	361	4433	PMT	-150	4433	100	-50
1135	ABC	791	4431	PMT	-306	4431	306	0
1135	ABC	775	4431	PMT	-160	4431	160	0


basically, the value in the AMT column is what the customer has paid.

take the example of sch_id 4431. customer paid only 466. but because it was accepted in 2 installments of 306 & 160, there are 2 rows for 4431. hope it clarifies.

im not sure how it can be achieved?

thanks for having a look.
Re: running total [message #433976 is a reply to message #433972] Sun, 06 December 2009 01:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is clear as mud.
You have a partition key good, now what about the order?

I don't understand how you come from
1135	ABC	791	4431	PMT	-466	4431	306	-160
1135	ABC	775	4431	PMT	-466	4431	160	-306

to
1135	ABC	791	4431	PMT	-306	4431	306	0
1135	ABC	775	4431	PMT	-160	4431	160	0

Remove all unecessary columns and restart.
Have customer_name, days, CLASS... anything to do in your current problem? If so how?

Regards
Michel
Re: running total [message #433980 is a reply to message #433976] Sun, 06 December 2009 02:54 Go to previous messageGo to next message
cherry
Messages: 56
Registered: December 2007
Member
thanks michel

for your query on how i arrived from A to B

amount is 466 (AMT Column). i just tried breaking it up as per the amounts in APPLIED Column ie 306 & 160

all the columns are required, but for resolving this, i can do away with CLASS column.
Re: running total [message #433981 is a reply to message #433980] Sun, 06 December 2009 03:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If I take the example of 4431 from your example I can say:

We group on all columns but the following 3 ones and:

1st result line:
AMT = -306 = - source applied
applied = source applied
outstanding = 0

2nd result line:
AMT = -160 = - source outstanding
outstanding = 0
applied = source applied

Where is the running total?

Regards
Michel
Re: [message #433994 is a reply to message #433981] Sun, 06 December 2009 04:48 Go to previous messageGo to next message
cherry
Messages: 56
Registered: December 2007
Member
Ok. may be the title i chose was not appropriate for this topic.

the value in the "final outstanding" is what we have to calculate. assume the column does not exist.

for 4431 the amount is -466

this amount was paid in 2 installments of 306 & 160.

i just want to calculate the what is the final outstanding amount ie. (-466) + (306+160)

thanks again.
Re: [message #433998 is a reply to message #433994] Sun, 06 December 2009 06:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't repeat the same thing.
I didn't understnand the first 2 times you said it, I will not understand the third one.

Do as I did. For each line of the result explain the value of each column.

Regards
Michel
Re: [message #434002 is a reply to message #433998] Sun, 06 December 2009 06:32 Go to previous messageGo to next message
cherry
Messages: 56
Registered: December 2007
Member
i've explained it the best possible way i could. sorry if you cant understand.

im trying to break the value (-466) in the "amt" column so that it matches that of the values in "amount_applied".
Re: [message #434003 is a reply to message #434002] Sun, 06 December 2009 06:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not explain, post what I asked and ONLY that:
Quote:
For each line of the result explain the value of each column.

Regards
Michel
Re: [message #434016 is a reply to message #434003] Sun, 06 December 2009 12:24 Go to previous messageGo to next message
cherry
Messages: 56
Registered: December 2007
Member
1135	ABC	791	4431	PMT	-466	4431	306	-160
1135	ABC	775	4431	PMT	0	4431	160	160


above is the expected output.

i dont want 466 getting repeated in every row, when the customer has only paid 466 once.

notice, when you sum the last column it will sum to 0, because there is nothing outstanding.

similarly for code 5555 payment received is 500. but only 200, 200 is recorded against the receipt of 500. sum of last column should show a balance of -100.

1135	ABC	182	5555	PMT	-500	5555	200	-300
1135	ABC	91	5555	PMT	0	5555	200	200


hope this clarifies?

[Updated on: Sun, 06 December 2009 12:28]

Report message to a moderator

Re: [message #434017 is a reply to message #434016] Sun, 06 December 2009 12:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sun, 06 December 2009 13:38
Do not explain, post what I asked and ONLY that:
Quote:
For each line of the result explain the value of each column.

Regards
Michel

Do you really think what you posted fit with what I asked?
I just want mathematical formula for each column value AS I DID.
Isn't this clear?

And as I also said:
Quote:
You have a partition key good, now what about the order

So what is the order of the input rows?
But first post what I asked 3 times that is:
line 1
column 1 value = XXX because it comes from "mathematical formula from input line and columns"
column 2 same thing
column 3 same thing
...
Line 2, same thing
And so on till the last line and column
Clear?

Regards
Michel

[Updated on: Sun, 06 December 2009 12:52]

Report message to a moderator

Re: [message #434020 is a reply to message #434017] Sun, 06 December 2009 13:39 Go to previous messageGo to next message
cherry
Messages: 56
Registered: December 2007
Member
i picked up one payment_schedule_id (4431) from the original select & showed the expected output.

as for the partition key, you can order by any column you want.

i've explained it in the simplest way possible. if you still cant understand, i give up.

btw, got the expected output using:
SELECT customer_number, days, payment_schedule_id, CLASS,
       DECODE (rn, 1, amt, NULL) amt, amount_applied,
       DECODE (rn, 1, amt + total_applied, NULL) final_outstanding, rn
  FROM (SELECT customer_number, days, payment_schedule_id, CLASS, amt,
               amount_applied,
               (SELECT SUM (amount_applied)
                  FROM t97
                 WHERE payment_schedule_id =
                                          b.payment_schedule_id)
                                                                total_applied,
               rn
          FROM (SELECT customer_number, days, payment_schedule_id, CLASS, amt,
                       amount_applied,
                       ROW_NUMBER () OVER (PARTITION BY payment_schedule_id ORDER BY days DESC)
                                                                           rn
                  FROM t97) b)


im sure, there are better ways of doing it?

[Updated on: Sun, 06 December 2009 13:40]

Report message to a moderator

Re: [message #434027 is a reply to message #434020] Sun, 06 December 2009 14:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
im sure, there are better ways of doing it?

Maybe but I still don't know what you want as you refuse to post what I ask.

Regards
Michel
Re: [message #434038 is a reply to message #434027] Sun, 06 December 2009 23:22 Go to previous messageGo to next message
cherry
Messages: 56
Registered: December 2007
Member
i agree problem descriptions that may seem very clear to me, may turn out to be unclear to those im asking for help. but in this case, i have provided structured test cases along with expected results.

also, i have the query which gives the required output.

Quote:
Maybe but I still don't know what you want as you refuse to post what I ask.
i still cant understand what is still left unanswered??!!??

running the query from my last post, gives the expected output. im sure it can be better written.

anyone else?
Re: [message #434039 is a reply to message #434038] Sun, 06 December 2009 23:28 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>running the query from my last post, gives the expected output. im sure it can be better written.
>anyone else?

If this is a request to tune a single SQL statement then

http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: [message #434042 is a reply to message #434038] Sun, 06 December 2009 23:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i still cant understand what is still left unanswered??!!??

Quote:
But first post what I asked 3 times that is:
line 1
column 1 value = XXX because it comes from "mathematical formula from input line and columns"
column 2 same thing
column 3 same thing
...
Line 2, same thing
And so on till the last line and column
Clear?

Do you see anything like this in your posts? Please show me where.

Regards
Michel
Re: [message #434050 is a reply to message #434042] Mon, 07 December 2009 01:11 Go to previous messageGo to next message
cherry
Messages: 56
Registered: December 2007
Member
Quote:

But first post what I asked 3 times that is:
line 1
column 1 value = XXX because it comes from "mathematical formula from input line and columns"
column 2 same thing
column 3 same thing
...
Line 2, same thing
And so on till the last line and column
Clear?


can you rephrase what you mean by:
"column 1 value = XXX because it comes from "mathematical formula from input line and columns"

lets take the case of 4431 here.
Re: [message #434058 is a reply to message #434050] Mon, 07 December 2009 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sun, 06 December 2009 10:23
If I take the example of 4431 from your example I can say:

We group on all columns but the following 3 ones and:

1st result line:
AMT = -306 = - source applied
applied = source applied
outstanding = 0

2nd result line:
AMT = -160 = - source outstanding
outstanding = 0
applied = source applied

Where is the running total?

Regards
Michel

I know it is wrong (in what you want to achieve but true regarding your figures) but this is this kind of things I want you provide.
"source" means value in current row (source of result data).

Regards
Michel

[Updated on: Mon, 07 December 2009 01:32]

Report message to a moderator

Re: [message #434061 is a reply to message #434058] Mon, 07 December 2009 01:37 Go to previous messageGo to next message
cherry
Messages: 56
Registered: December 2007
Member
Quote:

1st result line:
AMT = -306 = - source applied
applied = source applied
outstanding = 0

2nd result line:
AMT = -160 = - source outstanding
outstanding = 0
applied = source applied

Where is the running total?

you are right. in this case outstanding is 0.
& there is no running total.
Re: [message #434070 is a reply to message #434061] Mon, 07 December 2009 02:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But what I want is the generic formula:
result AMT = what for what?
applied = what for what?
outstanding = what for what?
And so on.

Regards
Michel
Re: [message #434082 is a reply to message #434070] Mon, 07 December 2009 02:34 Go to previous messageGo to next message
cherry
Messages: 56
Registered: December 2007
Member
i want to find the DIFFERENCE between the AMOUNT column & AMOUNT_APPLIED column, grouped on PAYMENT_SCHEDULE_ID

Re: [message #434084 is a reply to message #434082] Mon, 07 December 2009 02:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
WHAT IS THE MATHEMATICAL FORMULA?
Isn't this clear? No more word, just a formula AS I DID.
Can't you understand that?

Regards
Michel
Re: [message #434086 is a reply to message #434084] Mon, 07 December 2009 02:51 Go to previous messageGo to next message
cherry
Messages: 56
Registered: December 2007
Member
do not shout michele!!! help if you can.

i will seek help elsewhere, where there are less arrogant people who are willing to help, when i have provided EVERYTHING required.

there is nothing which i do not wish to tell you. no mathematical formula involved here.

you did not bother to look @ the results of my query also. that will give you an idea of what im trying to achieve.

thanks for your time, though!

[Updated on: Mon, 07 December 2009 02:54]

Report message to a moderator

Re: [message #434102 is a reply to message #434086] Mon, 07 December 2009 03:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
do not shout michele!!! help if you can.

I tried all over my Sunday but you didn't help me to help you.
I asked for a simple thing (and I gave an example of what I want) and you were unable to give it.
It was the fifth or sixth times I asked the same things, understand my irritation.

Regards
Michel
Re: [message #434116 is a reply to message #434102] Mon, 07 December 2009 04:05 Go to previous message
cherry
Messages: 56
Registered: December 2007
Member
http://forums.oracle.com/forums/thread.jspa?messageID=3950996#3950996

within 2 hrs of supplying the same information that i put up here, i got the solution.

Quote:
It was the fifth or sixth times I asked the same things, understand my irritation.


i do understand this, i think it was being over simplified when explained to you, and i've got a bad habit now in terms of thought process, but what your saying is very valid.

i like coming to this site because of people like barbara, jrowbottom, ross & few others whose name i cannot recall. at the same time there are others who's replies deter you from coming back to this forum. its just a constructive criticism.

all's well that ends well. thanks.
Previous Topic: Select the row if atleast 1 column = 0
Next Topic: converting to "\u" notation in Oracle
Goto Forum:
  


Current Time: Fri Dec 02 14:31:38 CST 2016

Total time taken to generate the page: 0.09901 seconds