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 Go to next message
OraFerro
Messages: 346
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 #672068 is a reply to message #671989] Mon, 01 October 2018 04:38 Go to previous messageGo to next message
OraFerro
Messages: 346
Registered: July 2011
Senior Member
The following is my contribution to solve the first part which is getting records from test_e and with each record the cc_amount in test_cc that has a date <= this records' date:
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')
  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;

Is this the best way? I was not able to use window function to get the same result?

Thanks,
Ferro
Re: Last and Sum in group by [message #672081 is a reply to message #672068] Mon, 01 October 2018 23:36 Go to previous messageGo to next message
OraFerro
Messages: 346
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 Go to previous message
Barbara Boehmer
Messages: 8911
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.
Previous Topic: List of Months between 2 dates
Next Topic: How to execute 5 procedures, one after another?
Goto Forum:
  


Current Time: Mon Apr 22 11:05:34 CDT 2019