Home » RDBMS Server » Server Administration » self joins (cr and dr totals)
self joins (cr and dr totals) [message #370861] Fri, 25 February 2000 00:48 Go to next message
alkakaneja
Messages: 10
Registered: February 2000
Junior Member
hi,

I have a table (transaction table of receipt master) that contains rec_no,date,adjustment no and adjustment amount and one flage describing cr or dr. I want to select sum of amount of cr flag and dr flag separately, the problm is that the number on which the sum is required exists in credit side only.
(i.e. i have credit adjustment number but no rec_no and i have to sum the adjustment amount of that adjustment number (flag cr) and sum of adjustment amount of the same rec_no (flag dr)).

i am able to do this with pl/sql procedure, but is there any way to do it with one sql statement.

thanks in advance

alka
Re: self joins (cr and dr totals) [message #370862 is a reply to message #370861] Fri, 25 February 2000 06:22 Go to previous messageGo to next message
Thierry Van der Auwera
Messages: 44
Registered: January 2000
Member
Hallo Alka,

I had first some problem understanding what you want to do.
I have made an example on how I think it must be.
When this is not correct, please post a correct example.

INPUT:
******
rec_no adjustment_no adjustment_amount flag
1 10 cr
1 20 cr
11 1 100 dr
11 1 200 dr
100 1000 cr
100 2000 cr
1100 100 10000 dr
1100 100 20000 dr

OUTPUT
******
REC_NO ADJUSTMENT_NO CR_AMO DR_AMO
11 1 30 300
1100 100 3000 30000

SELECT
MAX(rec_no) rec_no
,adjustment_no
,SUM(DECODE(flag,'CR',adjustment_amount,NULL)) cr_amo
,SUM(DECODE(flag,'DR',adjustment_amount,NULL)) dr_amo
FROM transaction
GROUP BY adjustment_no
;

Hope this helps

Greetings,

Thierry.
Re: self joins (cr and dr totals) [message #370864 is a reply to message #370862] Mon, 28 February 2000 02:22 Go to previous messageGo to next message
alkakaneja
Messages: 10
Registered: February 2000
Junior Member
thanks Thierry, but my input is a bit different
it is something like:
rec_no adjustment_no adj.amount flag
11 999 1000 cr
11 001 500 dr
11 002 500 dr
20 998 5000 cr
20 005 4000 dr

i am querrying on adjustment number (rec_no is not known)-
the output should be
rec_no adj_number(cr) cr_amount dr_amount
11 999 1000 1000
20 998 5000 4000
...

hope i am clear this time,

thanks in advance,

alka

(sorry, the other reply is wrong)!
Re: self joins (cr and dr totals) [message #370866 is a reply to message #370862] Mon, 28 February 2000 11:09 Go to previous message
Thierry Van der Auwera
Messages: 44
Registered: January 2000
Member
Hallo Alka,

Try following sql :

SELECT rec_no
,AVG(DECODE(flag,'CR',adjustment_no,NULL)) adj_no
,SUM(DECODE(flag,'CR',adjustment_amount,0)) cr_amount
,SUM(DECODE(flag,'DR',adjustment_amount,0)) dr_amount
FROM transaction tr
GROUP BY rec_no
;

This must be it. (When not then reply).

Greetings,

Thierry.
Previous Topic: how can I get Pro*c/c++?
Next Topic: Duplicate entries in a table
Goto Forum:
  


Current Time: Wed Oct 28 07:19:44 CDT 2020