【--4、去月保费
Execute Immediate 'truncate table jsxs_day_qy1 ';
insert into jsxs_day_qy1
select /*+parallel(4) */ level2comcode,
level3comcode,
riskcode,
newchnltype,
sum(PREMIUM) PREMIUM,
datetype
from (select /*+parallel(4) */ b.level2comcode,
b.level3comcode,
substr(a.RISKCODE, 1, 2) as riskcode,
(case
when x.newchnltype is null then
'00'
else
x.newchnltype
end) newchnltype,
(a.PREMIUM * a.EXCHRATE * a.COINSRATE / 100) AS PREMIUM, --原始保费收入
'STATDATE' as datetype
FROM WEB_LIST_CMAINORIGIN a, cd_com_all b, ods_cmain x
WHERE a.STATDATE between
add_months(trunc(sysdate - 1, 'mm'), -12) and
add_months(trunc(sysdate - 1), -12)
and a.COMCODE = b.comcode
and a.policyno = x.policyno(+)
UNION ALL
select /*+parallel(4) */ d.level2comcode,
d.level3comcode,
substr(c.RISKCODE, 1, 2) as riskcode,
(case
when y.newchnltype is null then
'00'
else
y.newchnltype
end) newchnltype,
(c.CHGPREMIUM * c.EXCHRATE * c.COINSRATE / 100) AS PREMIUM, --批改保费
'STATDATE' as datetype
FROM WEB_LIST_PMAIN c, cd_com_all d, ods_pmain y
WHERE c.STATDATE between
add_months(trunc(sysdate - 1, 'mm'), -12) and
add_months(trunc(sysdate - 1), -12)
and c.COMCODE = d.comcode
and c.endorseno = y.endorseno(+)
and c.CHGPREMIUM != 0
union all
select /*+parallel(4) */ e.level2comcode,
e.level3comcode,
substr(f.RISKCODE, 1, 2) as riskcode,
(case
when m.newchnltype is null then
'00'
else
m.newchnltype
end) newchnltype,
(f.PREMIUM * f.EXCHRATE * f.COINSRATE / 100) AS PREMIUM, --原始保费收入
'UNDERWRITEENDDATE' as datetype
FROM WEB_LIST_CMAINORIGIN f, cd_com_all e, ods_cmain m
WHERE f.UNDERWRITEENDDATE between
add_months(trunc(sysdate - 1, 'mm'), -12) and
add_months(trunc(sysdate - 1), -12)
and f.COMCODE = e.comcode
and f.policyno = m.policyno(+)
UNION ALL
select /*+parallel(4) */ h.level2comcode,
h.level3comcode,
substr(g.RISKCODE, 1, 2) as riskcode,
(case
when n.newchnltype is null then
'00'
else
n.newchnltype
end) newchnltype,
(g.CHGPREMIUM * g.EXCHRATE * g.COINSRATE / 100) AS PREMIUM, --批改保费
'UNDERWRITEENDDATE' as datetype
FROM WEB_LIST_PMAIN g, cd_com_all h, ods_pmain n
WHERE g.STATDATE between
add_months(trunc(sysdate - 1, 'mm'), -12) and
add_months(trunc(sysdate - 1), -12)
and g.COMCODE = h.comcode
and g.endorseno = n.endorseno(+)
and g.CHGPREMIUM != 0)
group by level2comcode,
level3comcode,
riskcode,
newchnltype,
datetype;
--5、去年保费
Execute Immediate 'truncate table jsxs_day_qn1';
insert into jsxs_day_qn1
select /*+parallel(4) */ level2comcode,
level3comcode,
riskcode,
newchnltype,
sum(PREMIUM) PREMIUM,
datetype
from (select /*+parallel(4) */ b.level2comcode,
b.level3comcode,
substr(a.RISKCODE, 1, 2) as riskcode,
(case
when x.newchnltype is null then
'00'
else
x.newchnltype
end) newchnltype,
(a.PREMIUM * a.EXCHRATE * a.COINSRATE / 100) AS PREMIUM, --原始保费收入
'STATDATE' as datetype
FROM WEB_LIST_CMAINORIGIN a, cd_com_all b, ods_cmain x
WHERE a.STATDATE between
add_months(trunc(sysdate - 1, 'yy'), -12) and
add_months(trunc(sysdate - 1), -12)
and a.COMCODE = b.comcode
and a.policyno = x.policyno(+)
UNION ALL
select /*+parallel(4) */ d.level2comcode,
d.level3comcode,
substr(c.RISKCODE, 1, 2) as riskcode,
(case
when y.newchnltype is null then
'00'
else
y.newchnltype
end) newchnltype,
(c.CHGPREMIUM * c.EXCHRATE * c.COINSRATE / 100) AS PREMIUM, --批改保费
'STATDATE' as datetype
FROM WEB_LIST_PMAIN c, cd_com_all d, ods_pmain y
WHERE c.STATDATE between
add_months(trunc(sysdate - 1, 'yy'), -12) and
add_months(trunc(sysdate - 1), -12)
and c.COMCODE = d.comcode
and c.endorseno = y.endorseno(+)
and c.CHGPREMIUM != 0
union all
select /*+parallel(4) */ e.level2comcode,
e.level3comcode,
substr(f.RISKCODE, 1, 2) as riskcode,
(case
when m.newchnltype is null then
'00'
else
m.newchnltype
end) newchnltype,
(f.PREMIUM * f.EXCHRATE * f.COINSRATE / 100) AS PREMIUM, --原始保费收入
'UNDERWRITEENDDATE' as datetype
FROM WEB_LIST_CMAINORIGIN f, cd_com_all e, ods_cmain m
WHERE f.UNDERWRITEENDDATE between
add_months(trunc(sysdate - 1, 'yy'), -12) and
add_months(trunc(sysdate - 1), -12)
and f.COMCODE = e.comcode
and f.policyno = m.policyno(+)
UNION ALL
select /*+parallel(4) */ h.level2comcode,
h.level3comcode,
substr(g.RISKCODE, 1, 2) as riskcode,
(case
when n.newchnltype is null then
'00'
else
n.newchnltype
end) newchnltype,
(g.CHGPREMIUM * g.EXCHRATE * g.COINSRATE / 100) AS PREMIUM, --批改保费
'UNDERWRITEENDDATE' as datetype
FROM WEB_LIST_PMAIN g, cd_com_all h, ods_pmain n
WHERE g.STATDATE between
add_months(trunc(sysdate - 1, 'yy'), -12) and
add_months(trunc(sysdate - 1), -12)
and g.COMCODE = h.comcode
and g.endorseno = n.endorseno(+)
and g.CHGPREMIUM != 0)
group by level2comcode,
level3comcode,
riskcode,
newchnltype,
datetype;
--6、去季度保费
Execute Immediate 'truncate table jsxs_day_qj1 ';
insert into jsxs_day_qj1
select /*+parallel(4) */ level2comcode,
level3comcode,
riskcode,
newchnltype,
sum(PREMIUM) PREMIUM,
datetype
from (select /*+parallel(4) */ b.level2comcode,
b.level3comcode,
substr(a.RISKCODE, 1, 2) as riskcode,
(case
when x.newchnltype is null then
'00'
else
x.newchnltype
end) newchnltype,
(a.PREMIUM * a.EXCHRATE * a.COINSRATE / 100) AS PREMIUM, --原始保费收入
'STATDATE' as datetype
FROM WEB_LIST_CMAINORIGIN a, cd_com_all b, ods_cmain x
WHERE a.STATDATE between
add_months(trunc(sysdate - 1, 'q'), -12) and
add_months(trunc(sysdate - 1), -12)
and a.COMCODE = b.comcode
and a.policyno = x.policyno(+)
UNION ALL
select /*+parallel(4) */ d.level2comcode,
d.level3comcode,
substr(c.RISKCODE, 1, 2) as riskcode,
(case
when y.newchnltype is null then
'00'
else
y.newchnltype
end) newchnltype,
(c.CHGPREMIUM * c.EXCHRATE * c.COINSRATE / 100) AS PREMIUM, --批改保费
'STATDATE' as datetype
FROM WEB_LIST_PMAIN c, cd_com_all d, ods_pmain y
WHERE c.STATDATE between
add_months(trunc(sysdate - 1, 'q'), -12) and
add_months(trunc(sysdate - 1), -12)
and c.COMCODE = d.comcode
and c.endorseno = y.endorseno(+)
and c.CHGPREMIUM != 0
union all
select /*+parallel(4) */ e.level2comcode,
e.level3comcode,
substr(f.RISKCODE, 1, 2) as riskcode,
(case
when m.newchnltype is null then
'00'
else
m.newchnltype
end) newchnltype,
(f.PREMIUM * f.EXCHRATE * f.COINSRATE / 100) AS PREMIUM, --原始保费收入
'UNDERWRITEENDDATE' as datetype
FROM WEB_LIST_CMAINORIGIN f, cd_com_all e, ods_cmain m
WHERE f.UNDERWRITEENDDATE between
add_months(trunc(sysdate - 1, 'q'), -12) and
add_months(trunc(sysdate - 1), -12)
and f.COMCODE = e.comcode
and f.policyno = m.policyno(+)
UNION ALL
select /*+parallel(4) */ h.level2comcode,
h.level3comcode,
substr(g.RISKCODE, 1, 2) as riskcode,
(case
when n.newchnltype is null then
'00'
else
n.newchnltype
end) newchnltype,
(g.CHGPREMIUM * g.EXCHRATE * g.COINSRATE / 100) AS PREMIUM, --批改保费
'UNDERWRITEENDDATE' as datetype
FROM WEB_LIST_PMAIN g, cd_com_all h, ods_pmain n
WHERE g.STATDATE between
add_months(trunc(sysdate - 1, 'q'), -12) and
add_months(trunc(sysdate - 1), -12)
and g.COMCODE = h.comcode
and g.endorseno = n.endorseno(+)
and g.CHGPREMIUM != 0)
group by level2comcode,
level3comcode,
riskcode,
newchnltype,
datetype;
--7、当季保费
Execute Immediate 'truncate table jsxs_day_dj1 ';
insert into jsxs_day_dj1
select /*+parallel(4) */ level2comcode,
level3comcode,
riskcode,
newchnltype,
sum(PREMIUM) PREMIUM,
datetype
from (select /*+parallel(4) */ b.level2comcode,
b.level3comcode,
substr(a.RISKCODE, 1, 2) as riskcode,
(case
when x.newchnltype is null then
'00'
else
x.newchnltype
end) newchnltype,
(a.PREMIUM * a.EXCHRATE * a.COINSRATE / 100) AS PREMIUM, --原始保费收入
'STATDATE' as datetype
FROM WEB_LIST_CMAINORIGIN a, cd_com_all b, ods_cmain x
WHERE a.STATDATE between trunc(trunc(sysdate - 1), 'q') and
trunc(sysdate - 1)
and a.COMCODE = b.comcode
and a.policyno = x.policyno(+)
UNION ALL
select /*+parallel(4) */ d.level2comcode,
d.level3comcode,
substr(c.RISKCODE, 1, 2) as riskcode,
(case
when y.newchnltype is null then
'00'
else
y.newchnltype
end) newchnltype,
(c.CHGPREMIUM * c.EXCHRATE * c.COINSRATE / 100) AS PREMIUM, --批改保费
'STATDATE' as datetype
FROM WEB_LIST_PMAIN c, cd_com_all d, ods_pmain y
WHERE c.STATDATE between trunc(trunc(sysdate - 1), 'q') and
trunc(sysdate - 1)
and c.COMCODE = d.comcode
and c.endorseno = y.endorseno(+)
and c.CHGPREMIUM != 0
union all
select /*+parallel(4) */ e.level2comcode,
e.level3comcode,
substr(f.RISKCODE, 1, 2) as riskcode,
(case
when m.newchnltype is null then
'00'
else
m.newchnltype
end) newchnltype,
(f.PREMIUM * f.EXCHRATE * f.COINSRATE / 100) AS PREMIUM, --原始保费收入
'UNDERWRITEENDDATE' as datetype
FROM WEB_LIST_CMAINORIGIN f, cd_com_all e, ods_cmain m
WHERE f.UNDERWRITEENDDATE between
trunc(trunc(sysdate - 1), 'q') and trunc(sysdate - 1)
and f.COMCODE = e.comcode
and f.policyno = m.policyno(+)
UNION ALL
select /*+parallel(4) */ h.level2comcode,
h.level3comcode,
substr(g.RISKCODE, 1, 2) as riskcode,
(case
when n.newchnltype is null then
'00'
else
n.newchnltype
end) newchnltype,
(g.CHGPREMIUM * g.EXCHRATE * g.COINSRATE / 100) AS PREMIUM, --批改保费
'UNDERWRITEENDDATE' as datetype
FROM WEB_LIST_PMAIN g, cd_com_all h, ods_pmain n
WHERE g.STATDATE between trunc(trunc(sysdate - 1), 'q') and
trunc(sysdate - 1)
and g.COMCODE = h.comcode
and g.endorseno = n.endorseno(+)
and g.CHGPREMIUM != 0)
group by level2comcode,
level3comcode,
riskcode,
newchnltype,
datetype;
--8、去年全年保费
Execute Immediate 'truncate table jsxs_day_qqn';
insert into jsxs_day_qqn
select /*+parallel(4) */ level2comcode,
level3comcode,
riskcode,
newchnltype,
sum(PREMIUM) PREMIUM,
datetype
from (select /*+parallel(4) */ b.level2comcode,
b.level3comcode,
substr(a.RISKCODE, 1, 2) as riskcode,
(case
when x.newchnltype is null then
'00'
else
x.newchnltype
end) newchnltype,
(a.PREMIUM * a.EXCHRATE * a.COINSRATE / 100) AS PREMIUM, --原始保费收入
'STATDATE' as datetype
FROM WEB_LIST_CMAINORIGIN a, cd_com_all b, ods_cmain x
WHERE a.STATDATE between
add_months(trunc(sysdate - 1, 'yy'), -12) and
trunc(sysdate - 1, 'yy') - 1
and a.COMCODE = b.comcode
and a.policyno = x.policyno(+)
UNION ALL
select /*+parallel(4) */ d.level2comcode,
d.level3comcode,
substr(c.RISKCODE, 1, 2) as riskcode,
(case
when y.newchnltype is null then
'00'
else
y.newchnltype
end) newchnltype,
(c.CHGPREMIUM * c.EXCHRATE * c.COINSRATE / 100) AS PREMIUM, --批改保费
'STATDATE' as datetype
FROM WEB_LIST_PMAIN c, cd_com_all d, ods_pmain y
WHERE c.STATDATE between
add_months(trunc(sysdate - 1, 'yy'), -12) and
trunc(sysdate - 1, 'yy') - 1
and c.COMCODE = d.comcode
and c.endorseno = y.endorseno(+)
and c.CHGPREMIUM != 0
union all
select /*+parallel(4) */ e.level2comcode,
e.level3comcode,
substr(f.RISKCODE, 1, 2) as riskcode,
(case
when m.newchnltype is null then
'00'
else
m.newchnltype
end) newchnltype,
(f.PREMIUM * f.EXCHRATE * f.COINSRATE / 100) AS PREMIUM, --原始保费收入
'UNDERWRITEENDDATE' as datetype
FROM WEB_LIST_CMAINORIGIN f, cd_com_all e, ods_cmain m
WHERE f.UNDERWRITEENDDATE between
add_months(trunc(sysdate - 1, 'yy'), -12) and
trunc(sysdate - 1, 'yy') - 1
and f.COMCODE = e.comcode
and f.policyno = m.policyno(+)
UNION ALL
select /*+parallel(4) */ h.level2comcode,
h.level3comcode,
substr(g.RISKCODE, 1, 2) as riskcode,
(case
when n.newchnltype is null then
'00'
else
n.newchnltype
end) newchnltype,
(g.CHGPREMIUM * g.EXCHRATE * g.COINSRATE / 100) AS PREMIUM, --批改保费
'UNDERWRITEENDDATE' as datetype
FROM WEB_LIST_PMAIN g, cd_com_all h, ods_pmain n
WHERE g.STATDATE between
add_months(trunc(sysdate - 1, 'yy'), -12) and
trunc(sysdate - 1, 'yy') - 1
and g.COMCODE = h.comcode
and g.endorseno = n.endorseno(+)
and g.CHGPREMIUM != 0)
group by level2comcode,
level3comcode,
riskcode,
newchnltype,
datetype;
--9、前年全年保费
Execute Immediate 'truncate table jsxs_day_qiannianquan';
insert into jsxs_day_qiannianquan
select /*+parallel(4) */ level2comcode,
level3comcode,
riskcode,
newchnltype,
sum(PREMIUM) PREMIUM,
datetype
from (select /*+parallel(4) */ b.level2comcode,
b.level3comcode,
substr(a.RISKCODE, 1, 2) as riskcode,
(case
when x.newchnltype is null then
'00'
else
x.newchnltype
end) newchnltype,
(a.PREMIUM * a.EXCHRATE * a.COINSRATE / 100) AS PREMIUM, --原始保费收入
'STATDATE' as datetype
FROM WEB_LIST_CMAINORIGIN a, cd_com_all b, ods_cmain x
WHERE a.STATDATE between
add_months(trunc(sysdate - 1, 'yy'), -24) and
add_months(trunc(sysdate - 1, 'yy'), -12) - 1
and a.COMCODE = b.comcode
and a.policyno = x.policyno(+)
UNION ALL
select /*+parallel(4) */ d.level2comcode,
d.level3comcode,
substr(c.RISKCODE, 1, 2) as riskcode,
(case
when y.newchnltype is null then
'00'
else
y.newchnltype
end) newchnltype,
(c.CHGPREMIUM * c.EXCHRATE * c.COINSRATE / 100) AS PREMIUM, --批改保费
'STATDATE' as datetype
FROM WEB_LIST_PMAIN c, cd_com_all d, ods_pmain y
WHERE c.STATDATE between
add_months(trunc(sysdate - 1, 'yy'), -24) and
add_months(trunc(sysdate - 1, 'yy'), -12) - 1
and c.COMCODE = d.comcode
and c.endorseno = y.endorseno(+)
and c.CHGPREMIUM != 0
union all
select /*+parallel(4) */ e.level2comcode,
e.level3comcode,
substr(f.RISKCODE, 1, 2) as riskcode,
(case
when m.newchnltype is null then
'00'
else
m.newchnltype
end) newchnltype,
(f.PREMIUM * f.EXCHRATE * f.COINSRATE / 100) AS PREMIUM, --原始保费收入
'UNDERWRITEENDDATE' as datetype
FROM WEB_LIST_CMAINORIGIN f, cd_com_all e, ods_cmain m
WHERE f.UNDERWRITEENDDATE between
add_months(trunc(sysdate - 1, 'yy'), -24) and
add_months(trunc(sysdate - 1, 'yy'), -12) - 1
and f.COMCODE = e.comcode
and f.policyno = m.policyno(+)
UNION ALL
select /*+parallel(4) */ h.level2comcode,
h.level3comcode,
substr(g.RISKCODE, 1, 2) as riskcode,
(case
when n.newchnltype is null then
'00'
else
n.newchnltype
end) newchnltype,
(g.CHGPREMIUM * g.EXCHRATE * g.COINSRATE / 100) AS PREMIUM, --批改保费
'UNDERWRITEENDDATE' as datetype
FROM WEB_LIST_PMAIN g, cd_com_all h, ods_pmain n
WHERE g.STATDATE between
add_months(trunc(sysdate - 1, 'yy'), -24) and
add_months(trunc(sysdate - 1, 'yy'), -12) - 1
and g.COMCODE = h.comcode
and g.endorseno = n.endorseno(+)
and g.CHGPREMIUM != 0)
group by level2comcode,
level3comcode,
riskcode,
newchnltype,
datetype;】改为高斯DB
最新发布
本文介绍如何使用SQL语句获取当前月份的第一天和最后一天，并转换为数字格式，便于进行日期范围的筛选。
扫一扫
1
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
