Home » SQL & PL/SQL » SQL & PL/SQL » Last and Sum in group by (11.1.2.10)
Last and Sum in group by [message #671989] |
Thu, 27 September 2018 05:43 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Hi All,
I have 3 tables that contain information about transaction history. I need to get all records from a certain table (test_e) until MyDate. And with each record of test_e I need to get
1- the amount that corresponds to the maximum date until Mydate from another table (test_cc)
2- and the sum of all amounts for records until Mydate from the third table (test_wth).
So for example if I have:
create table test_cc
(
cc_id number,
cc_date date,
cc_amount number,
fk_ar_id number
);
create table test_e
(
e_id number,
e_date date,
fk_ar_id number
);
create table test_wth
(
wth_id number,
wth_date date,
wth_amount number,
fk_ar_id number
);
insert ALL
into test_cc values(1000,sysdate - 300, 50000, 555)
into test_cc values(2000,sysdate - 270, 40000, 555)
into test_cc values(3000,sysdate - 210, 30000, 555)
into test_cc values(4000,sysdate - 180, 20000, 555)
into test_cc values(5000,to_date('01-11-2015','dd-mm-yyyy'), 20000, 555)
into test_e values(100,sysdate - 300, 555)
into test_e values(200,sysdate - 260, 555)
into test_e values(300,sysdate - 190, 555)
into test_e values(400,sysdate - 170, 555)
into test_e values(500,to_date('20-11-2017','dd-mm-yyyy'), 555)
into test_wth values(90,to_date('01-11-2017','dd-mm-yyyy'), 10000.5, 555)
into test_wth values(10,sysdate - 300, 10000.5, 555)
into test_wth values(20,sysdate - 261, 10000.5, 555)
into test_wth values(30,sysdate - 262, 10000.5, 555)
into test_wth values(40,sysdate - 170, 10000.5, 555)
into test_wth values(50,sysdate - 172, 10000.5, 555)
into test_wth values(60,sysdate - 173, 10000.5, 555)
into test_wth values(70,sysdate - 195, 10000.5, 555)
into test_wth values(80,sysdate - 111, 10000.5, 555)
select * from dual;
The following query will get all transactions for a certain date (01-01-2018):
select FK_AR_ID, ID, trans_date, Trans_type, cc_amount, wth_amount FROM
(
select FK_AR_ID, CC_ID ID, CC_DATE trans_date, CC_AMOUNT cc_amount, null wth_amount,'cc' Trans_type from TEST_CC
union
select FK_AR_ID, e_ID ID, e_DATE trans_date, null cc_amount, null wth_amount, 'e' Trans_type from test_e
union
select FK_AR_ID, wth_ID ID, wth_DATE trans_date, null cc_amount, wth_AMOUNT wth_amount, 'wth' Trans_type from test_wth
)
where trans_date <= to_date('01-01-2018','dd-mm-yyyy')
;
;
FK_AR_ID ID TRANS_DATE TRANS_TYPE CC_AMOUNT WTH_AMOUNT
555 2000 31/12/2017 1:37:51 PM cc 40000 (null)
555 1000 01/12/2017 1:37:51 PM cc 50000 (null)
555 100 01/12/2017 1:37:51 PM e (null) (null)
555 10 01/12/2017 1:37:51 PM wth (null) 10000.5
555 500 20/11/2017 12:00:00 AM e (null) (null)
555 90 01/11/2017 12:00:00 AM wth (null) 10000.5
555 5000 01/11/2015 12:00:00 AM cc 20000 (null)
But what I need is all transactions of TRANS_TYPE= e, and with each record the cc_amount in test_cc that has a date <= this records' date and the total wth_amount from test_wth with date<= this records' date. So for 01-01-2018 I need the results as follows:
FK_AR_ID TRANS_DATE TRANS_TYPE CC_AMOUNT WTH_AMOUNT
555 01/12/2017 11:37:24 AM e 50000 20001
555 20/11/2017 12:00:00 AM e 20000 10000.5
Thanks,
Ferro
|
|
|
|
Re: Last and Sum in group by [message #672081 is a reply to message #672068] |
Mon, 01 October 2018 23:36 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Hi All,
Following is the final query I managed to do but I need an expert opinion for a better way. Can analytic functions help here?
select cc_part.*, wth_part.wth_amount
from
(
select e_data.*, cc.CC_AMOUNT FROM
(
select e.*,max(cc.CC_DATE ) max_cc_date
FROM test_e e, test_cc cc
where e.fk_ar_id = cc.FK_AR_ID
--and e.fk_ar_id = 555
and e.e_date <= <= to_date('01-01-2018','dd-mm-yyyy')-- :MyDate
and cc_date <= e.e_date
group by e_id,e_date,e.FK_AR_ID
) e_data,
TEST_CC cc
where cc.CC_DATE = e_data.max_cc_date
) cc_part,
(
select e.*,sum(wth.WTH_AMOUNT) wth_amount
FROM test_e e, TEST_WTH wth
where e.fk_ar_id = wth.FK_AR_ID
--and e.fk_ar_id = 555
and e.e_date <= <= to_date('01-01-2018','dd-mm-yyyy')--:MyDate
and wth.WTH_DATE <= e.e_date
group by e_id,e_date,e.FK_AR_ID
) wth_part
where cc_part.E_ID = wth_part.E_ID;
Thanks,
Ferro
|
|
|
Re: Last and Sum in group by [message #673199 is a reply to message #672081] |
Sun, 11 November 2018 05:19 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
The following returns the same result as your final query without unnecessary sub-queries.
SCOTT@orcl_12.1.0.2.0> select e.e_id, e.e_date, e.fk_ar_id,
2 max(cc.cc_date) max_cc_date, cc.cc_amount,
3 sum(wth.wth_amount) sum_wth_amount
4 from test_e e, test_cc cc, test_wth wth
5 where e.fk_ar_id = cc.fk_ar_id
6 and e.e_date <= to_date('01-01-2018','dd-mm-yyyy')
7 and cc.cc_date <= e.e_date
8 and e.fk_ar_id = wth.fk_ar_id
9 and e.e_date <= to_date('01-01-2018','dd-mm-yyyy')
10 and wth.wth_date <= e.e_date
11 group by e.e_id, e.e_date, e.fk_ar_id, cc.cc_amount
12 /
E_ID E_DATE FK_AR_ID MAX_CC_DATE CC_AMOUNT SUM_WTH_AMOUNT
---------- ---------------------- ---------- ---------------------- ---------- --------------
500 20/11/2017 12:00:00 AM 555 01/11/2015 12:00:00 AM 20000 10000.5
1 row selected.
|
|
|
Goto Forum:
Current Time: Fri Sep 27 11:10:32 CDT 2024
|