select * from(
with vtailratio_tmp as (
select distinct d.*,e.OPERATE_WAY,e.min_tail_fare,e.YEAR_DAY,e.TAIL_COMMISSION_RATIO,e.AREA_FLAG,e.MIN_HOSTING,e.ENABLE_DATE,e.MAX_HOSTING
from distributor_fund_protocol d,
(select a.fund_code,a.distributor_code,
nvl(c.OPERATE_WAY,b.OPERATE_WAY) OPERATE_WAY,
0.00 min_tail_fare,
decode(nvl(c.YEAR_DAY,b.YEAR_DAY),
'365', '1',
'2', '0',
'3', '3',
'0', '2'
) YEAR_DAY,
nvl(c.TAIL_COMMISSION_RATIO,b.TAIL_COMMISSION_RATIO) TAIL_COMMISSION_RATIO,
nvl(c.AREA_FLAG,b.AREA_FLAG) AREA_FLAG,
nvl(c.MIN_HOSTING,b.MIN_HOSTING) MIN_HOSTING,
nvl(c.ENABLE_DATE,b.ENABLE_DATE) ENABLE_DATE,
nvl(c.MAX_HOSTING,b.MAX_HOSTING) MAX_HOSTING
from (select fund_code,distributor_code from fund_info,distributor_info) a,
(select * from TAIL_COMMISSION_FARE_SET_ORI where DISTRIBUTOR_CODE = '***') b,
(select * from TAIL_COMMISSION_FARE_SET_ORI where DISTRIBUTOR_CODE <> '***') c
where a.fund_code = c.fund_code(+)
and a.DISTRIBUTOR_CODE = c.DISTRIBUTOR_CODE(+)
and a.fund_code = b.fund_code(+)
)e
where d.fund_code = e.fund_code
and d.distributor_code = e.distributor_code
)
select a.distributor_code distributor_code,di.distributor_name,a.fund_code fundcode,fi.fund_name,to_char(replace(sum(BALANCE), ','),'FM999,999,999,999,990.00')balance,to_char(replace(sum(SHARES), ','),'FM999,999,999,999,990.00') shares,to_char(replace(avg(balance), ','),'FM999,999,999,999,990.00') avgbalance,to_char(replace(sum(fee), ','),'FM999,999,999,999,990.00') fee, sum(income) income, avg(shares) avgshares,
tfa.min_tail_fare,case when sum(fee)>=tfa.min_tail_fare then sum(fee) else 0 end paid_fee,
to_char(nvl(TAIL_COMMISSION_RATIO,0)*100,'fm999999990.0099999')||'%' TAIL_COMMISSION_RATIO
from(
select distributor_code, fund_code, TRANSACTION_CFM_DATE,sum(balance) balance, sum(shares) shares, sum(fee) fee,
sum(TAIL_COMMISSION_RATIO) TAIL_COMMISSION_RATIO,sum(income) income,avg(balance) avgbalance,avg(shares) avgshares
from(
select distributor_code,
fund_code,
TRANSACTION_CFM_DATE,
share_class,
sum(BALANCE) BALANCE,
sum(SHARES) SHARES,
sum(round(BALANCE /(case when YEAR_DAY>0 then YEAR_DAY else year.YearDays end)* nvl(TAIL_COMMISSION_RATIO, 0), 2)) fee,
max(nvl(TAIL_COMMISSION_RATIO, 0)) TAIL_COMMISSION_RATIO,
sum(nvl(income, 0)) income,
avg(BALANCE) avgbalance,
avg(SHARES) avgshares
from(
SELECT A.distributor_code,
A.fund_code,
'A' share_class,
A.TRANSACTION_CFM_DATE,
decode(b.OPERATE_WAY,
'0',a.balance,
'1',nvl(A.balance, 0) - nvl(A.REINVEST_BALANCE, 0),
'2',nvl(A.ORI_HOLD_BALANCE, 0),
'3',nvl(A.ORI_HOLD_BALANCE, 0) - nvl(A.ORI_REINVEST_BALANCE, 0)) BALANCE,
decode(b.OPERATE_WAY,
'0',a.shares,
'1',nvl(A.shares, 0) - nvl(A.REINVEST_SHARE, 0),
'2',nvl(A.shares, 0),
'3',nvl(A.shares, 0) - nvl(A.REINVEST_SHARE, 0)) SHARES,
B.TAIL_COMMISSION_RATIO,
A.income,
nvl(b.YEAR_DAY,0) YEAR_DAY
FROM(
select a.distributor_code,
a.fund_code,
'A' share_class,
a.TRANSACTION_CFM_DATE,
a.HOLD_DATE,
a.HOLD_RATIO,
nvl(a.income, 0) income,
nvl(a.REINVEST_SHARE, 0) REINVEST_SHARE,
nvl(a.REINVEST_BALANCE, 0) REINVEST_BALANCE,
nvl(a.ORI_HOLD_BALANCE, 0) ORI_HOLD_BALANCE,
NVL(A.ORI_REINVEST_BALANCE, 0) ORI_REINVEST_BALANCE,
case when b.ACC_INCOME_TO_SHARE_FLAG = '0' or /** #TACode || **/ '94'||a.distributor_code = '48002' then nvl(a.HOLD_BALANCE, 0)
else nvl(a.HOLD_BALANCE, 0) + nvl(a.income, 0) end balance,
case when b.ACC_INCOME_TO_SHARE_FLAG = '0' or /** #TACode || **/ '94'||a.distributor_code = '48002' then nvl(a.HOLD_SHARE, 0)
else nvl(a.HOLD_SHARE, 0) + nvl(a.income, 0) end shares
from FUND_SALE_STAT a,fund_info b
WHERE a.INDIVIDUAL_OR_INSTITUTION = '*'
and A.TRANSACTION_CFM_DATE >= {"origin" :"param", "field" :"dateStart","where" :"%s"}
and a.TRANSACTION_CFM_DATE <= {"origin" :"param", "field" :"dateEnd","where" :"%s"}
and a.fund_code = b.fund_code
) A,
(
select fund_code, distributor_code, AREA_FLAG,
MIN_HOSTING, MAX_HOSTING, TAIL_COMMISSION_RATIO, ENABLE_DATE,OPERATE_WAY,YEAR_DAY,
last_value(end_date)over(partition by fund_code, distributor_code, ENABLE_DATE
order by end_date
rows between unbounded preceding
and unbounded following) end_date
from(select fund_code, distributor_code, AREA_FLAG,
MIN_HOSTING, MAX_HOSTING, TAIL_COMMISSION_RATIO, ENABLE_DATE,OPERATE_WAY,YEAR_DAY,
nvl(lead(ENABLE_DATE)over(partition by fund_code, distributor_code
order by ENABLE_DATE, MIN_HOSTING),
'20991231') end_date
from vtailratio_tmp where AREA_FLAG in ('0','1')
)
) B
where A.fund_code = B.fund_code
AND A.distributor_code = B.distributor_code
and A.HOLD_DATE >= B.ENABLE_DATE
and A.HOLD_DATE < B.end_date
AND decode(b.OPERATE_WAY,
'0',a.balance,
'1',nvl(A.balance, 0) - nvl(A.REINVEST_BALANCE, 0),
'2',nvl(A.ORI_HOLD_BALANCE, 0),
'3',nvl(A.ORI_HOLD_BALANCE, 0) - nvl(A.ORI_REINVEST_BALANCE, 0)) > B.MIN_HOSTING
AND decode(b.OPERATE_WAY,
'0',a.balance,
'1',nvl(A.balance, 0) - nvl(A.REINVEST_BALANCE, 0),
'2',nvl(A.ORI_HOLD_BALANCE, 0),
'3',nvl(A.ORI_HOLD_BALANCE, 0) - nvl(A.ORI_REINVEST_BALANCE, 0)) <= B.MAX_HOSTING
and DECODE(B.AREA_FLAG,'2', a.HOLD_RATIO, '1', DECODE(b.OPERATE_WAY, '0', SHARES, '1', A.SHARES - A.REINVEST_SHARE, '2', SHARES, '3', A.SHARES - A.REINVEST_SHARE), DECODE(b.OPERATE_WAY, '0', A.BALANCE, '1', A.BALANCE - A.REINVEST_BALANCE, '2', a.ORI_HOLD_BALANCE, '3', A.ORI_HOLD_BALANCE - A.ORI_REINVEST_BALANCE)) >= B.MIN_HOSTING
and DECODE(B.AREA_FLAG,'2', a.HOLD_RATIO, '1', DECODE(b.OPERATE_WAY, '0', SHARES, '1', A.SHARES - A.REINVEST_SHARE, '2', SHARES, '3', A.SHARES - A.REINVEST_SHARE), DECODE(b.OPERATE_WAY, '0', A.BALANCE, '1', A.BALANCE - A.REINVEST_BALANCE, '2', a.ORI_HOLD_BALANCE, '3', A.ORI_HOLD_BALANCE - A.ORI_REINVEST_BALANCE)) < B.MAX_HOSTING
and not exists(
select *
from GRADING_FUND_PLAN tsc
where tsc.MAIN_FUND_CODE = A.fund_code
and tsc.SECTION_FUND_CODE = '******'
)
and a.transaction_cfm_date >= {"origin" :"param", "field" :"dateStart","where" :"%s"}
and a.transaction_cfm_date <= {"origin" :"param", "field" :"dateEnd","where" :"%s"}
{"origin" :"param", "field":"agencyCodeList", "where" :" and a.distributor_code in (%s)"}
{"origin" :"param", "field" :"fundCodeList", "where" :" and a.fund_code in (%s)"}
/** and #WhereSql **/
) hz ,
(select case when substr({"origin" :"param", "field" :"dateStart","where" :"%s"}, 1, 4)=substr({"origin" :"param", "field" :"dateEnd","where" :"%s"}, 1, 4) and mod(substr({"origin" :"param", "field" :"dateEnd","where" :"%s"}, 1, 4),4)=0 then '366' else '365' end YearDays from dual) year
where GetSysValueRX('System','TailSegment','0')=0
and GetSysValueRX('System','TailMode','1')=1
group by distributor_code,fund_code,share_class,TRANSACTION_CFM_DATE
union all
select distributor_code,
fund_code,
TRANSACTION_CFM_DATE,
share_class,
sum(BALANCE) BALANCE,
sum(SHARES) SHARES,
sum(round(BALANCE / (case when YEAR_DAY>0 then YEAR_DAY else year.YearDays end) * nvl(TAIL_COMMISSION_RATIO, 0), 2)) fee,
max(nvl(TAIL_COMMISSION_RATIO, 0)) TAIL_COMMISSION_RATIO,
sum(nvl(income, 0)) income,
avg(BALANCE) avgbalance,
avg(SHARES) avgshares
from(
SELECT a.distributor_code,
a.fund_code,
'A' share_class,
a.TRANSACTION_CFM_DATE,
decode(e.OPERATE_WAY,
'0',a.balance,
'1',nvl(a.balance, 0) - nvl(a.REINVEST_BALANCE, 0),
'2',nvl(a.ORI_HOLD_BALANCE, 0),
'3',nvl(a.ORI_HOLD_BALANCE, 0) - nvl(a.ORI_REINVEST_BALANCE, 0)) BALANCE,
decode(e.OPERATE_WAY,
'0',a.shares,
'1',nvl(a.shares, 0) - nvl(a.REINVEST_SHARE, 0),
'2',nvl(a.shares, 0),
'3',nvl(a.shares, 0) - nvl(a.REINVEST_SHARE, 0)) SHARES,
E.TAIL_COMMISSION_RATIO,
a.income,
nvl(e.YEAR_DAY,0) YEAR_DAY
FROM(
select a.distributor_code,
a.fund_code,
'A' share_class,
a.TRANSACTION_CFM_DATE,
a.HOLD_DATE,
a.HOLD_RATIO,
nvl(a.income, 0) income,
nvl(a.REINVEST_SHARE, 0) REINVEST_SHARE,
nvl(a.REINVEST_BALANCE, 0) REINVEST_BALANCE,
nvl(a.ORI_HOLD_BALANCE, 0) ORI_HOLD_BALANCE,
NVL(A.ORI_REINVEST_BALANCE, 0) ORI_REINVEST_BALANCE,
case when b.ACC_INCOME_TO_SHARE_FLAG = '0' or /** #TACode || **/ '94'||a.distributor_code = '48002' then nvl(a.HOLD_BALANCE, 0)
else nvl(a.HOLD_BALANCE, 0) + nvl(a.income, 0) end balance,
case when b.ACC_INCOME_TO_SHARE_FLAG = '0' or /** #TACode || **/ '94'||a.distributor_code = '48002' then nvl(a.HOLD_SHARE, 0)
else nvl(a.HOLD_SHARE, 0) + nvl(a.income, 0) end shares
from FUND_SALE_STAT a,fund_info b
WHERE a.INDIVIDUAL_OR_INSTITUTION = '*'
and A.TRANSACTION_CFM_DATE >= {"origin" :"param", "field" :"dateStart","where" :"%s"}
and a.TRANSACTION_CFM_DATE <= {"origin" :"param", "field" :"dateEnd","where" :"%s"}
and a.fund_code = b.fund_code
) a,
vtailratio_tmp tfa,
(select A.TRANSACTION_CFM_DATE,b.TAIL_COMMISSION_RATIO,
a.distributor_code,
a.fund_code,
B.ENABLE_DATE,
B.end_date,
b.YEAR_DAY,
b.OPERATE_WAY
from(
select a.distributor_code,
a.fund_code,
'A' share_class,
a.TRANSACTION_CFM_DATE,
a.HOLD_DATE,
a.HOLD_RATIO,
nvl(a.income, 0) income,
nvl(a.REINVEST_SHARE, 0) REINVEST_SHARE,
nvl(a.REINVEST_BALANCE, 0) REINVEST_BALANCE,
nvl(a.ORI_HOLD_BALANCE, 0) ORI_HOLD_BALANCE,
NVL(A.ORI_REINVEST_BALANCE, 0) ORI_REINVEST_BALANCE,
case when b.ACC_INCOME_TO_SHARE_FLAG = '0' or /** #TACode || **/ '94'||a.distributor_code = '48002' then nvl(a.HOLD_BALANCE, 0)
else nvl(a.HOLD_BALANCE, 0) + nvl(a.income, 0) end balance,
case when b.ACC_INCOME_TO_SHARE_FLAG = '0' or /** #TACode || **/ '94'||a.distributor_code = '48002' then nvl(a.HOLD_SHARE, 0)
else nvl(a.HOLD_SHARE, 0) + nvl(a.income, 0) end shares
from FUND_SALE_STAT a,fund_info b
WHERE a.INDIVIDUAL_OR_INSTITUTION = '*'
and A.TRANSACTION_CFM_DATE >= {"origin" :"param", "field" :"dateStart","where" :"%s"}
and a.TRANSACTION_CFM_DATE <= {"origin" :"param", "field" :"dateEnd","where" :"%s"}
and a.fund_code = b.fund_code
) a,
(select fund_code, distributor_code, AREA_FLAG,
MIN_HOSTING, MAX_HOSTING, TAIL_COMMISSION_RATIO, ENABLE_DATE,OPERATE_WAY,YEAR_DAY,
last_value(end_date)over(partition by fund_code, distributor_code, ENABLE_DATE
order by end_date
rows between unbounded preceding
and unbounded following) end_date
from(
select fund_code, distributor_code, AREA_FLAG,
MIN_HOSTING, MAX_HOSTING, TAIL_COMMISSION_RATIO, ENABLE_DATE,OPERATE_WAY,YEAR_DAY,
nvl(lead(ENABLE_DATE)over(partition by fund_code, distributor_code
order by ENABLE_DATE, MIN_HOSTING),
'20991231') end_date
from vtailratio_tmp where AREA_FLAG in ('0','1')
)
) b
WHERE b.fund_code = a.fund_code
and b.distributor_code = a.distributor_code
and a.HOLD_DATE >= B.ENABLE_DATE
and a.HOLD_DATE < B.end_date
group by a.distributor_code,
a.fund_code,
B.AREA_FLAG,
B.TAIL_COMMISSION_RATIO,
B.MIN_HOSTING,
B.MAX_HOSTING,
B.ENABLE_DATE,
B.end_date,A.TRANSACTION_CFM_DATE,B.YEAR_DAY,b.OPERATE_WAY
having DECODE(B.AREA_FLAG,'2', avg(a.HOLD_RATIO), '1', AVG(DECODE(b.OPERATE_WAY, '0', SHARES, '1', A.SHARES - A.REINVEST_SHARE, '2', SHARES, '3', A.SHARES - A.REINVEST_SHARE)), AVG(DECODE(b.OPERATE_WAY, '0', A.BALANCE, '1', A.BALANCE - A.REINVEST_BALANCE, '2', a.ORI_HOLD_BALANCE, '3', A.ORI_HOLD_BALANCE - A.ORI_REINVEST_BALANCE))) >= B.MIN_HOSTING
and DECODE(B.AREA_FLAG,'2', avg(a.HOLD_RATIO), '1', AVG(DECODE(b.OPERATE_WAY, '0', SHARES, '1', A.SHARES - A.REINVEST_SHARE, '2', SHARES, '3', A.SHARES - A.REINVEST_SHARE)), AVG(DECODE(b.OPERATE_WAY, '0', A.BALANCE, '1', A.BALANCE - A.REINVEST_BALANCE, '2', a.ORI_HOLD_BALANCE, '3', A.ORI_HOLD_BALANCE - A.ORI_REINVEST_BALANCE))) < B.MAX_HOSTING
) e
WHERE a.distributor_code = e.distributor_code(+)
and a.fund_code = e.fund_code(+) AND A.TRANSACTION_CFM_DATE = E.TRANSACTION_CFM_DATE
and a.fund_code = tfa.fund_code
and a.distributor_code = tfa.distributor_code
and a.HOLD_DATE >= e.ENABLE_DATE(+)
and a.HOLD_DATE < e.end_date(+)
and not exists(select *
from GRADING_FUND_PLAN tsc
where tsc.MAIN_FUND_CODE = a.fund_code
and tsc.SECTION_FUND_CODE = '******')
and a.transaction_cfm_date >= {"origin" :"param", "field" :"dateStart","where" :"%s"}
and a.transaction_cfm_date <= {"origin" :"param", "field" :"dateEnd","where" :"%s"}
{"origin" :"param", "field":"agencyCodeList", "where" :" and a.distributor_code in (%s)"}
{"origin" :"param", "field" :"fundCodeList", "where" :" and a.fund_code in (%s)"}
/** and #WhereSql **/
) hz,
(select case when substr({"origin" :"param", "field" :"dateStart","where" :"%s"}, 1, 4)=substr({"origin" :"param", "field" :"dateEnd","where" :"%s"}, 1, 4) and mod(substr({"origin" :"param", "field" :"dateEnd","where" :"%s"}, 1, 4),4)=0 then '366' else '365' end YearDays from dual) year
where GetSysValueRX('System','TailSegment','0')=0
and GetSysValueRX('System','TailMode','1')=2
group by distributor_code,fund_code,share_class,TRANSACTION_CFM_DATE
union all
select distributor_code, fund_code, TRANSACTION_CFM_DATE, share_class, BALANCE, SHARES, fee, TAIL_COMMISSION_RATIO, income, avgbalance, avgshares
from(
select distributor_code,
fund_code,
TRANSACTION_CFM_DATE,
share_class,
sum(BALANCE) BALANCE,
sum(SHARES) SHARES,
sum(nvl((
select round(sum(DECODE(AREA_FLAG,'2',ROUND(SUM(GREATEST(LEAST(m.MAX_HOSTING-m.MIN_HOSTING,HZA.BALANCE-m.MIN_HOSTING),0)*TAIL_COMMISSION_RATIO/(case when hzA.YEAR_DAY>0 then hzA.YEAR_DAY else year.YearDays end)),2),
'1',ROUND(SUM(GREATEST(LEAST(m.MAX_HOSTING-m.MIN_HOSTING,HZA.SHARES-m.MIN_HOSTING),0)*hzA.NAV*TAIL_COMMISSION_RATIO/(case when hzA.YEAR_DAY>0 then hzA.YEAR_DAY else year.YearDays end)),2),
ROUND(SUM(greatest(least(m.MAX_HOSTING - m.MIN_HOSTING,hzA.BALANCE- m.MIN_HOSTING),0) * TAIL_COMMISSION_RATIO / 365)),2)),2)
from(
select fund_code, distributor_code, AREA_FLAG,
MIN_HOSTING, MAX_HOSTING, TAIL_COMMISSION_RATIO, ENABLE_DATE,
last_value(end_date)over(partition by fund_code, distributor_code, ENABLE_DATE
order by end_date
rows between unbounded preceding
and unbounded following) end_date
from(
select fund_code, distributor_code, AREA_FLAG,
MIN_HOSTING, MAX_HOSTING, TAIL_COMMISSION_RATIO, ENABLE_DATE,
nvl(lead(ENABLE_DATE)over(partition by fund_code, distributor_code
order by ENABLE_DATE, MIN_HOSTING),
'20991231') end_date
from vtailratio_tmp
where AREA_FLAG in ('0','1'))
) m,
(select case when substr({"origin" :"param", "field" :"dateStart","where" :"%s"}, 1, 4)=substr({"origin" :"param", "field" :"dateEnd","where" :"%s"}, 1, 4) and mod(substr({"origin" :"param", "field" :"dateEnd","where" :"%s"}, 1, 4),4)=0 then '366' else '365' end YearDays from dual) year
where distributor_code = hzA.distributor_code
and fund_code = hzA.fund_code
and hzA.HOLD_DATE >= ENABLE_DATE
and hzA.HOLD_DATE < end_date
GROUP BY AREA_FLAG),0)) fee,
0 TAIL_COMMISSION_RATIO,
sum(nvl(income,0)) income,
avg(BALANCE) avgbalance,
avg(SHARES) avgshares
from(
SELECT A.HOLD_DATE,
A.distributor_code,
A.fund_code,
'A' share_class,
A.TRANSACTION_CFM_DATE,
decode(tfa.OPERATE_WAY,
'0',a.balance,
'1',nvl(A.balance, 0) - nvl(A.REINVEST_BALANCE, 0),
'2',nvl(A.ORI_HOLD_BALANCE, 0),
'3',nvl(A.ORI_HOLD_BALANCE, 0) - nvl(A.ORI_REINVEST_BALANCE, 0)) BALANCE,
decode(tfa.OPERATE_WAY,
'0',a.shares,
'1',nvl(A.shares, 0) - nvl(A.REINVEST_SHARE, 0),
'2',nvl(A.shares, 0),
'3',nvl(A.shares, 0) - nvl(A.REINVEST_SHARE, 0)) SHARES,
A.income ,D.NAV NAV,
nvl(tfa.YEAR_DAY,0) YEAR_DAY
FROM(
select a.distributor_code,
a.fund_code,
'A' share_class,
a.TRANSACTION_CFM_DATE,
a.HOLD_DATE,
a.HOLD_RATIO,
nvl(a.income, 0) income,
nvl(a.REINVEST_SHARE, 0) REINVEST_SHARE,
nvl(a.REINVEST_BALANCE, 0) REINVEST_BALANCE,
nvl(a.ORI_HOLD_BALANCE, 0) ORI_HOLD_BALANCE,
NVL(A.ORI_REINVEST_BALANCE, 0) ORI_REINVEST_BALANCE,
case when b.ACC_INCOME_TO_SHARE_FLAG = '0' or /** #TACode || **/ '94'||a.distributor_code = '48002' then nvl(a.HOLD_BALANCE, 0)
else nvl(a.HOLD_BALANCE, 0) + nvl(a.income, 0) end balance,
case when b.ACC_INCOME_TO_SHARE_FLAG = '0' or /** #TACode || **/ '94'||a.distributor_code = '48002' then nvl(a.HOLD_SHARE, 0)
else nvl(a.HOLD_SHARE, 0) + nvl(a.income, 0) end shares
from FUND_SALE_STAT a,fund_info b
WHERE a.INDIVIDUAL_OR_INSTITUTION = '*'
and A.TRANSACTION_CFM_DATE >= {"origin" :"param", "field" :"dateStart","where" :"%s"}
and a.TRANSACTION_CFM_DATE <= {"origin" :"param", "field" :"dateEnd","where" :"%s"}
and a.fund_code = b.fund_code
) A, vtailratio_tmp tfa , NET_VALUE D
where A.fund_code = tfa.fund_code
AND A.distributor_code = tfa.distributor_code
AND A.fund_code = D.fund_code
AND D.TRANSACTION_CFM_DATE = (select min(fd.TRANSACTION_CFM_DATE) from NET_VALUE fd where fd.TRANSACTION_CFM_DATE >= a.TRANSACTION_CFM_DATE and fd.fund_code = a.fund_code )
and not exists(select *
from GRADING_FUND_PLAN tsc
where tsc.MAIN_FUND_CODE = A.fund_code
and tsc.SECTION_FUND_CODE = '******')
and a.transaction_cfm_date >= {"origin" :"param", "field" :"dateStart","where" :"%s"}
and a.transaction_cfm_date <= {"origin" :"param", "field" :"dateEnd","where" :"%s"}
{"origin" :"param", "field":"agencyCodeList", "where" :" and a.distributor_code in (%s)"}
{"origin" :"param", "field" :"fundCodeList", "where" :" and a.fund_code in (%s)"}
/** and #WhereSql **/
) hzA
group by distributor_code,fund_code,share_class,TRANSACTION_CFM_DATE
)
where GetSysValueRX('System', 'TailSegment', '0') = '1'
AND GetSysValueRX('System', 'TailMode', '1') = '1'
union all
select A.distributor_code, A.fund_code,
A.TRANSACTION_CFM_DATE,'A' share_class, 0 BALANCE, 0 SHARES, round(nvl(A.TRANSFER_FEE,0)*tfb.distributor_ratio,2) fee,
0 TAIL_COMMISSION_RATIO, 0 income, 0 avgbalance, 0 avgshares
from(
select t.*
from(
select * from trade_confirm
union all
select * from TRADE_CONFIRM_LOG
) t
) A,fee_belong tfb
where A.fund_code = tfb.fund_code
and A.distributor_code = tfb.distributor_code
and tfb.fee_type = '31'
and A.CHECK_RESULT = '1'
and A.BUSINESS_CODE = '124'
and A.TRANSFER_FEE>0
and GetSysValueRX('Report','TailsIncludeProfit','0')='1'
/** and #WhereSql **/
and a.transaction_cfm_date >= {"origin" :"param", "field" :"dateStart","where" :"%s"}
and a.transaction_cfm_date <= {"origin" :"param", "field" :"dateEnd","where" :"%s"}
{"origin" :"param", "field":"agencyCodeList", "where" :" and a.distributor_code in (%s)"}
{"origin" :"param", "field" :"fundCodeList", "where" :" and a.fund_code in (%s)"}
)
group by distributor_code,fund_code,TRANSACTION_CFM_DATE
)a,
(select fund_code,distributor_code, min_tail_fare
from(
select fund_code,distributor_code,nvl(min_tail_fare,0) min_tail_fare,
ROW_NUMBER() OVER(PARTITION BY fund_code , distributor_code ORDER BY fund_code DESC,distributor_code DESC) RANK
from vtailratio_tmp) tfa
where RANK=1
) tfa,fund_info fi,distributor_info di
where a.fund_code = tfa.fund_code
and a.fund_code = fi.fund_code
and a.distributor_code = di.distributor_code
and a.distributor_code = tfa.distributor_code
group by a.distributor_code,di.distributor_name,a.fund_code,fi.fund_name,tfa.min_tail_fare ,TAIL_COMMISSION_RATIO
)order by fundcode,distributor_code给这段sql划分层次使人更容易理解
最新发布
本文介绍了SQL中的几个实用技巧，包括使用nvl函数处理空值、通过case when语句计算特定条件下的汇总值、利用decode和sign函数进行条件判断、以及通过子查询删除重复记录的方法。此外还提供了一个复杂的SQL查询案例，展示了如何联接多个表并按日期排序。
扫一扫
2888
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
