DO
BEGIN
DECLARE V_E_MONTH NVARCHAR(6); --获取开始月份
DECLARE MONTH NVARCHAR(6); --获取开始月份
DECLARE V_S_DATE1 NVARCHAR(8) ; --开始日期
DECLARE V_S_DATE NVARCHAR(8) ; --开始日期
DECLARE V_E_DATE NVARCHAR(8) ; --截止日期
DECLARE PSY_S_DATE nvarchar(8); --上月开始日期
DECLARE PSY_E_DATE nvarchar(8); --上月开始日期
DECLARE PSN_S_DATE nvarchar(8); --上年开始日期
DECLARE PSN_S_DATE1 nvarchar(8); --上年开始日期
DECLARE PSN_E_DATE nvarchar(8); --上年截止日期
DECLARE V_ORG_CODE NVARCHAR(20);
DECLARE V_ORG NVARCHAR(64);
DECLARE V_USER NVARCHAR(8);
--输入月份
V_E_MONTH = '${p_month}';
--V_E_MONTH = '202403';
--当月
MONTH = LEFT(to_char(CURRENT_DATE,'yyyymmdd'),6);
---输入年月等于当前月
IF MONTH = V_E_MONTH THEN
--结束日期
V_E_DATE = to_char(add_days(CURRENT_DATE,-1),'yyyymmdd');
-----输入年月小于当前月
ELSE
--结束日期
V_E_DATE = to_char(to_dats(last_day(to_date(V_E_MONTH ||'01','yyyymmdd'))),'yyyymmdd');
END IF;
V_S_DATE=LEFT(:V_E_DATE,6)||'01'; --获取查询月1号
V_S_DATE1=LEFT(:V_E_DATE,4)||'0101'; --获取查询年1号
PSY_E_DATE = to_char(add_months(to_date(:V_E_DATE),-1),'yyyymmdd'); --获取上月截止日期
PSY_S_DATE=LEFT(:PSY_E_DATE,6)||'01'; --获取上月1号
PSN_E_DATE = to_char(add_months(to_date(:V_E_DATE),-12),'yyyymmdd'); --获取上年截止日期
PSN_S_DATE=LEFT(:PSN_E_DATE,6)||'01'; --获取上年1号
PSN_S_DATE1=LEFT(:PSN_E_DATE,4)||'0101'; --获取上年1号
SELECT
"L4",
replace(replace("L4_TEXT",'养猪',''),'省区','') AS "L4_TEXT",
CASE WHEN sum("BXFY_ZCX")=0 THEN 0
ELSE sum("BXPF_ZCX")/sum("BXFY_ZCX")
END AS "PFL_ZXC", --赔付率-政策险
CASE WHEN SUM("SJBASW_QTY")=0 THEN 0
ELSE 10000*SUM("BXPF_ZCX")/SUM("SJBASW_QTY")
END AS "DTPFJE", --单头赔付金额
CASE WHEN SUM("YBAJE")=0 THEN 0
ELSE SUM("BXPF_JE")/SUM("YBAJE")
END AS "HKL" --回款率
FROM( SELECT
"L4",
"L4_TEXT",
ROUND(sum("BXFY_ZCX")/10000,2)+
ROUND(SUM(CASE WHEN "CALDAY"=:V_E_DATE THEN "BXFY_FZC" ELSE 0 END)/10000,2) AS "BXFY_TOTAL", --总保费
ROUND(sum("BXPF_JE")/10000,2) AS "BXPF_TOTAL", --赔付金额
ROUND(sum("BXPF_JE")/10000-ROUND(sum("BXFY_ZCX")/10000,2)-
ROUND(SUM(CASE WHEN "CALDAY"=:V_E_DATE THEN "BXFY_FZC" ELSE 0 END)/10000,2)) AS "ZSY_TOTAL", --总收益
ROUND(SUM(CASE WHEN "CALDAY"=:V_E_DATE THEN "BXFY_FZC" ELSE 0 END)/10000,2) AS "BXFY_FZC", --保险费用-非政策险
ROUND(SUM("BXPF_FZC")/10000,2) AS "BXPF_FZC", --赔付金额-非政策险
ROUND(sum("BXFY_ZCX")/10000,2) AS "BXFY_ZCX", --保费-政策险
ROUND(sum("BXPF_ZCX")/10000,2) AS "BXPF_ZCX", --赔付金额-政策险
ROUND(sum("BXPF_ZCX")/10000-sum("BXFY_ZCX")/10000,2) AS "ZSY_ZCX", --净收益-政策险
CASE WHEN sum("BXFY_ZCX")=0 THEN 0
ELSE sum("BXPF_ZCX")/sum("BXFY_ZCX")
END AS "PFL_ZXC", --赔付率-政策险
SUM("QTY_SW") AS "SW_QTY", --总死淘数
SUM("YBASW_QTY") AS "YBASW_QTY", --应报案死淘数
SUM("SJBASW_QTY") AS "SJBASW_QTY", --实际报案死淘数
CASE WHEN SUM("YBASW_QTY")=0 THEN 0
ELSE SUM("SJBASW_QTY")/SUM("YBASW_QTY")
END AS "BAL", --报案率
SUM("SWPFJE") AS "YBAJE", --应报案金额
SUM("BXPF_JE") AS "BXPF_JE", --保险赔付金额
SUM(CASE WHEN "CALDAY"=:V_E_DATE THEN "QTY" ELSE 0 END) AS "QTY", --存栏数
SUM(CASE WHEN "CALDAY"=:V_E_DATE THEN "QTY_WZCX" ELSE 0 END) AS "QTY_WZCX", --无政策险存栏
SUM(CASE WHEN "CALDAY"=:V_E_DATE THEN "QTY" ELSE 0 END)-SUM(CASE WHEN "CALDAY"=:V_E_DATE THEN "QTY_WZCX" ELSE 0 END) AS "QTY_YZCX", --有政策险存栏
SUM(CASE WHEN "CALDAY"=:V_E_DATE THEN "QTY_YZCXYTB" ELSE 0 END) AS "QTY_YZCXYTB", --有政策险已投保存栏
CASE WHEN SUM(CASE WHEN "CALDAY"=:V_E_DATE THEN "QTY" ELSE 0 END)-SUM(CASE WHEN "CALDAY"=:V_E_DATE THEN "QTY_WZCX" ELSE 0 END)=0 THEN 0
ELSE SUM(CASE WHEN "CALDAY"=:V_E_DATE THEN "QTY_YZCXYTB" ELSE 0 END)/( SUM(CASE WHEN "CALDAY"=:V_E_DATE THEN "QTY" ELSE 0 END)-SUM(CASE WHEN "CALDAY"=:V_E_DATE THEN "QTY_WZCX" ELSE 0 END))
END AS "FGL" --覆盖率
FROM"SAPHANADB"."/BIC/AZGFZ_D0282"
WHERE "CALDAY">=:V_S_DATE AND "CALDAY"<=:V_E_DATE
AND "L2"='90000000'
GROUP BY
"L4",
"L4_TEXT"
/*
UNION ALL
SELECT
"L4",
"L4_TEXT",
ROUND(sum("BXFY_ZCX")/10000,2)+ ROUND( SUM(CASE WHEN "CALDAY"=:V_E_DATE THEN "BXFY_FZCX" ELSE 0 END)/10000 ,2) AS "BXFY_TOTAL", --总保费
ROUND(sum("BXPF_JE")/10000,2) AS "BXPF_TOTAL", --赔付金额
ROUND(sum("BXPF_JE")/10000-sum("BXFY_MT")/10000,2) AS "ZSY_TOTAL", --总收益
ROUND( SUM(CASE WHEN "CALDAY"=:V_E_DATE THEN "BXFY_FZCX" ELSE 0 END)/10000 ,2) AS "BXFY_FZC", --保险费用-非政策险
ROUND(SUM("BXPF_FZCX")/10000,2) AS "BXPF_FZC", --赔付金额-非政策险
ROUND(sum("BXFY_ZCX")/10000,2) AS "BXFY_ZCX", --保费-政策险
ROUND(sum("BXPF_ZCX")/10000,2) AS "BXPF_ZCX", --赔付金额-政策险
ROUND(sum("BXPF_ZCX")/10000-sum("BXFY_ZCX")/10000,2) AS "ZSY_ZCX", --净收益-政策险
CASE WHEN sum("BXFY_ZCX")=0 THEN 0
ELSE sum("BXPF_ZCX")/sum("BXFY_ZCX")
END AS "PFL_ZXC", --赔付率-政策险
SUM("SW_QTY") AS "SW_QTY", --总死淘数
SUM("YBASW_QTY") AS "YBASW_QTY", --应报案死淘数
SUM("SJBASW_QTY") AS "SJBASW_QTY", --实际报案死淘数
CASE WHEN SUM("YBASW_QTY")=0 THEN 0
ELSE SUM("SJBASW_QTY")/SUM("YBASW_QTY")
END AS "BAL", --报案率
SUM("YBAJE") AS "YBAJE", --应报案金额
SUM("BXPF_JE") AS "BXPF_JE", --赔付金额
SUM(CASE WHEN "CALDAY"=:V_E_DATE THEN "QTY" ELSE 0 END) AS "QTY", --存栏数
SUM(CASE WHEN "CALDAY"=:V_E_DATE THEN"QTY_WZCX" ELSE 0 END) AS "QTY_WZCX", --无政策险存栏
SUM(CASE WHEN "CALDAY"=:V_E_DATE THEN"QTY_YZCX" ELSE 0 END) AS "QTY_YZCX", --有政策险存栏
SUM(CASE WHEN "CALDAY"=:V_E_DATE THEN"QTY_YZCXYTB" ELSE 0 END) AS "QTY_YZCXYTB", --有政策险已投保存栏
CASE WHEN SUM(CASE WHEN "CALDAY"=:V_E_DATE THEN"QTY_YZCX" ELSE 0 END)=0 THEN 0
ELSE SUM(CASE WHEN "CALDAY"=:V_E_DATE THEN"QTY_YZCXYTB" ELSE 0 END)/ SUM(CASE WHEN "CALDAY"=:V_E_DATE THEN"QTY_YZCX" ELSE 0 END)
END AS "FGL" --覆盖率
FROM"SAPHANADB"."/BIC/AZGYF_D0392"
WHERE "CALDAY">=:V_S_DATE AND "CALDAY"<=:V_E_DATE
AND "L2"='90000000'
GROUP BY
"L4",
"L4_TEXT"
*/
)
WHERE "L4_TEXT"<>'全南生产部'
GROUP BY
"L4",
replace(replace("L4_TEXT",'养猪',''),'省区','')
;
END ，假如这段代码是存在一个字符串当中，使用Java解析这段字符串，并且获取当中的表名
扫一扫
1802
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
