Home » SQL & PL/SQL » SQL & PL/SQL » Calculate problem , help me
Calculate problem , help me [message #672389] Sat, 13 October 2018 00:15 Go to next message
zhaoquer
Messages: 35
Registered: October 2013
Member
create table agent_rate(
agent_id varchar(20),
v_rate NUMBER(18, 4),
m_rate NUMBER(18, 4),
fee NUMBER(18, 4)
);
insert into agent_rate (AGENT_ID, V_RATE, M_RATE, FEE)
values ('1000', 0.0300, 0.0300, 1.0000);
insert into agent_rate (AGENT_ID, V_RATE, M_RATE, FEE)
values ('100001', 0.0400, 0.0500, 1.2000);
insert into agent_rate (AGENT_ID, V_RATE, M_RATE, FEE)
values ('10000101', 0.0600, 0.0900, 1.5000);
-------------
create table trans(
tradeNo char(11),
agent_id varchar(20),
card_type char(1),
txn_amt NUMBER(18, 2)
);
insert into trans (TRADENO, AGENT_ID, CARD_TYPE, TXN_AMT)
values ('20181013001', '1000', 'm', 100.00);
insert into trans (TRADENO, AGENT_ID, CARD_TYPE, TXN_AMT)
values ('20181013002', '1000', 'v', 200.00);
insert into trans (TRADENO, AGENT_ID, CARD_TYPE, TXN_AMT)
values ('20181013004', '100001', 'm', 500.00);
insert into trans (TRADENO, AGENT_ID, CARD_TYPE, TXN_AMT)
values ('20181013005', '100001', 'v', 700.00);
insert into trans (TRADENO, AGENT_ID, CARD_TYPE, TXN_AMT)
values ('20181013004', '1000', 'm', 500.00);
insert into trans (TRADENO, AGENT_ID, CARD_TYPE, TXN_AMT)
values ('20181013005', '1000', 'v', 700.00);
-----------
create table agent_amount(
tradeNo char(11),
agent_id varchar(20),
rate_amount NUMBER(18, 2),
Fee_amount NUMBER(18, 2)
);
insert into agent_amount (TRADENO, AGENT_ID, RATE_AMOUNT, FEE_AMOUNT)
values ('20181013001', '1000', 2.00, 0.20);
insert into agent_amount (TRADENO, AGENT_ID, RATE_AMOUNT, FEE_AMOUNT)
values ('20181013002', '1000', 2.00, 0.20);
insert into agent_amount (TRADENO, AGENT_ID, RATE_AMOUNT, FEE_AMOUNT)
values ('20181013004', '1000', 10.00, 0.20);
insert into agent_amount (TRADENO, AGENT_ID, RATE_AMOUNT, FEE_AMOUNT)
values ('20181013005', '1000', 7.00, 0.20);
insert into agent_amount (TRADENO, AGENT_ID, RATE_AMOUNT, FEE_AMOUNT)
values ('20181013004', '100001', 20.00, 0.30);
insert into agent_amount (TRADENO, AGENT_ID, RATE_AMOUNT, FEE_AMOUNT)
values ('20181013005', '100001', 14.00, 0.30);
commit;
Hi All,
Please help me. Table agent_amount result is what I want from table agent_rate and trans. Thanks in advance!
  • Attachment: 1013.PNG
    (Size: 136.15KB, Downloaded 601 times)

Report message to a moderator

Re: Calculate problem , help me [message #672390 is a reply to message #672389] Sat, 13 October 2018 00:28 Go to previous messageGo to next message
zhaoquer
Messages: 35
Registered: October 2013
Member
agent_id 1000: rate_amount = (0.04-0.03)*txn_amt (visa)
agent_id 1000: rate_amount = (0.05-0.03)*txn_amt (master)
agent_id 1000: fee_amount = 1.2-1.0

agent_id 100001: rate_amount = (0.06-0.04)*txn_amt (visa)
agent_id 100001: rate_amount = (0.09-0.05)*txn_amt (master)
agent_id 100001: fee_amount = 1.5-1.2

Report message to a moderator

Re: Calculate problem , help me [message #672391 is a reply to message #672390] Sat, 13 October 2018 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68447
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Clear as mud.
Specify in details each column of the result table from the input tables.

In addition, explain each line of your second post.
Quote:
agent_id 1000: rate_amount = (0.04-0.03)*txn_amt (visa)
Where does 0.04 comes from?
Where does 0.03 comes from?
Where does "visa" comes from? And how does it impact the result?

Report message to a moderator

Re: Calculate problem , help me [message #672393 is a reply to message #672391] Sat, 13 October 2018 01:27 Go to previous messageGo to next message
zhaoquer
Messages: 35
Registered: October 2013
Member
Michel,
Thanks for your reply.
"visa" means card_type is v in trans table.
"master" means card_type is m in trans table.
v_rate is visa rate.
m_rate is master rate.
For agent_id 1000:
If the transrecord's card_type is v(visa),
rate_amount = (0.04-0.03)*txn_amt
0.04 is the v_rate of agent_id 100001
0.03 is the v_rate of agent_id 1000

agent_id 100001 is the next level agent of agent_id 1000;

Report message to a moderator

Re: Calculate problem , help me [message #672394 is a reply to message #672393] Sat, 13 October 2018 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68447
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

My first request was: "Specify in details each column of the result table from the input tables.".

Why for "agent_id 1000" you take into account "agent_id 100001" values? And why "100001" and not "10000101" or "2" or any other one?

Specify, specify, specify!

Report message to a moderator

Re: Calculate problem , help me [message #672395 is a reply to message #672394] Sat, 13 October 2018 01:54 Go to previous messageGo to next message
zhaoquer
Messages: 35
Registered: October 2013
Member
Hi Michel,
I'd rather change table agent_rate structure , maybe you can understand . Sorry for wasting you time. (English is not my first language ,i try my best to make it clearly)
agent_id 1000 is the parent id of 100001 .

drop table agent_rate;
create table agent_rate(
agent_id varchar(20),
parent_id varchar(20),
v_rate NUMBER(18, 4),
m_rate NUMBER(18, 4),
fee NUMBER(18, 4)
);
insert into agent_rate (AGENT_ID, PARENT_ID, V_RATE, M_RATE, FEE)
values ('1000', '', 0.0300, 0.0300, 1.0000);
insert into agent_rate (AGENT_ID, PARENT_ID, V_RATE, M_RATE, FEE)
values ('100001', '1000', 0.0400, 0.0500, 1.2000);
insert into agent_rate (AGENT_ID, PARENT_ID, V_RATE, M_RATE, FEE)
values ('10000101', '100001', 0.0600, 0.0900, 1.5000);
commit;

Report message to a moderator

Re: Calculate problem , help me [message #672396 is a reply to message #672395] Sat, 13 October 2018 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68447
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And how agent_id and parent_id are involved?

You MUST specify, with words (and maybe formulas), each column of your result.

Fills:
tradeNo is ... defined by ...
agent_id is ... defined by ...
rate_amount is ... defined by ...
Fee_amount is ... defined by ...

Report message to a moderator

Re: Calculate problem , help me [message #672397 is a reply to message #672396] Sat, 13 October 2018 02:24 Go to previous messageGo to next message
zhaoquer
Messages: 35
Registered: October 2013
Member
1,tradeNo is value of tradeNo from table trans.
2,agent_id is value of agent_id from table trans.
3,rate_amount is txn_amt * (next level agent rate - current agent rate)
when card_type is m use m_rate , card_type is v use v_rate.

eg: tradeNo:20181013001 , agent_id: 1000 , card_type is m (use m_rate)
rate_amount: 100*(0.05-0.03) = 2
Fee_amount : 1.2 - 1.0 = 0.2

4,Fee_amount is next level agent fee - current agen fee.

Report message to a moderator

Re: Calculate problem , help me [message #672398 is a reply to message #672397] Sat, 13 October 2018 02:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68447
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I think "next level" means "parent".
So now you have the clear definition of each column there is no problem, just join input tables on agent_id and make the operations.

Report message to a moderator

Re: Calculate problem , help me [message #672399 is a reply to message #672398] Sat, 13 October 2018 02:59 Go to previous messageGo to next message
zhaoquer
Messages: 35
Registered: October 2013
Member
Thank you Michel.

Report message to a moderator

Re: Calculate problem , help me [message #672400 is a reply to message #672399] Sat, 13 October 2018 03:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68447
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Have you find the solution? If so, please post it for future readers.

[Updated on: Sat, 13 October 2018 03:04]

Report message to a moderator

Re: Calculate problem , help me [message #672401 is a reply to message #672400] Sat, 13 October 2018 04:01 Go to previous message
zhaoquer
Messages: 35
Registered: October 2013
Member
I did it ,thanks again.
select t.tradeno,
       t.agent_id,
       V1.fee_amount,
       CASE
         WHEN t.card_type = 'v' THEN
          t.txn_amt * V1.v_rate
         WHEN t.card_type = 'm' THEN
          t.txn_amt * V1.m_rate
       END AS rate_amount
  from trans t
  left join (SELECT t1.agent_id,
                    (t2.v_rate - t1.v_rate) AS v_rate,
                    (t2.m_rate - t1.m_Rate) AS m_rate,
                    (t2.fee - t1.fee) AS fee_amount
               FROM agent_rate t1
              CROSS JOIN agent_rate t2
              WHERE t1.agent_id = t2.parent_id) V1
    on t.agent_id = V1.agent_id;

Mod: Please put your code in [code] code here [/code] code tags

[Updated on: Sat, 13 October 2018 05:29] by Moderator

Report message to a moderator

Previous Topic: Complex SQL Quiries for me
Next Topic: Duplicating Excel COUPNCD function in PLSQL.
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun May 28 18:26:00 CDT 2023
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.