Home » SQL & PL/SQL » SQL & PL/SQL » Calculate problem , help me
Calculate problem , help me Sat, 13 October 2018 00:15
 zhaoquer Messages: 34Registered: 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(
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(
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,
• Attachment: 1013.PNG
Re: Calculate problem , help me [message #672390 is a reply to message #672389] Sat, 13 October 2018 00:28
 zhaoquer Messages: 34Registered: 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
Re: Calculate problem , help me [message #672391 is a reply to message #672390] Sat, 13 October 2018 00:48
 Michel Cadot Messages: 65848Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator

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

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?

Re: Calculate problem , help me [message #672393 is a reply to message #672391] Sat, 13 October 2018 01:27
 zhaoquer Messages: 34Registered: October 2013 Member
Michel,
"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;
Re: Calculate problem , help me [message #672394 is a reply to message #672393] Sat, 13 October 2018 01:35
 Michel Cadot Messages: 65848Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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!

Re: Calculate problem , help me [message #672395 is a reply to message #672394] Sat, 13 October 2018 01:54
 zhaoquer Messages: 34Registered: 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;
Re: Calculate problem , help me [message #672396 is a reply to message #672395] Sat, 13 October 2018 01:58
 Michel Cadot Messages: 65848Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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 ...

Re: Calculate problem , help me [message #672397 is a reply to message #672396] Sat, 13 October 2018 02:24
 zhaoquer Messages: 34Registered: October 2013 Member
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.
Re: Calculate problem , help me [message #672398 is a reply to message #672397] Sat, 13 October 2018 02:29
 Michel Cadot Messages: 65848Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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.

Re: Calculate problem , help me [message #672399 is a reply to message #672398] Sat, 13 October 2018 02:59
 zhaoquer Messages: 34Registered: October 2013 Member
Thank you Michel.
Re: Calculate problem , help me [message #672400 is a reply to message #672399] Sat, 13 October 2018 03:03
 Michel Cadot Messages: 65848Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 zhaoquer Messages: 34Registered: 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:

Current Time: Fri Oct 19 23:10:56 CDT 2018