select unique t2.policyno "保单号码",
t8.STARTDATE "起保日期",
t8.ENDDATE "终保日期",
t1.damagestartdate "出险日期",
t1.REGISTNO "报案号码",
t1.REPORTDATE "报案日期",
t1.CLAIMNO "立案号码",
t3.ACCIDENTNO "事故号码",
t1.CLAIMDATE "立案日期",
t1.underwriteenddate "核赔通过日期",
t1.CANCELDATE "注销/拒赔日期",
t1.endcasedate "结案日期",
t1.INSUREDNAME "被保人名称",
t1.CHNLDESC "渠道类型",
t1.BUSINESSNATUREDESC "业务来源",
t1.AGENTNAME "代理人姓名",
t1.comcode "机构代码",
t1.comname "机构名称",
t1.riskcode "险种代码",
t1.riskname "险种名称",
t1.damagereasondesc "出险原因",
t1.sumclaim "估损金额",
t1.settledloss*t1.exchrate*t1.coinsrate/100 "结案金额",
t2.DAMAGEADDRESS "出险地点",
t4.SURVEYORID "查勘人ID",
t3.OPERATORID "立案人ID",
t5.ASSESSERID "定损车损ID",
t10.ASSESSERID "定损人伤ID",
t11.ASSESSERID "定损物损ID",
t5.VERIFIERID "核损人ID",
t6.COMPENSATORID "理算员ID",
t7.UNDERWRITEID "核赔人ID",
t9.ENDCASERID "结案员ID",
COUNT(t2.REGISTNO)-SUM(CASE WHEN t2.CANCELDATE IS NOT NULL THEN 1 ELSE 0 END) "出险次数"
from web_list_lp t1,
MID_CAR_LREGIST t2,
MID_CAR_LCLAIM t3,
ODS_CAR_LSURVEY t4,
ODS_CAR_LASSESS_CAR t5,
ODS_CAR_LCOMPENSATE_LOG t6,
ODS_CAR_LCOMPENSATE t7,
WEB_LIST_CMAIN t8,
ODS_CAR_LENDCASE t9,
ODS_CAR_LASSESS_PERSON t10,
ODS_CAR_LASSESS_PROP t11
where t1.REGISTNO = t2.REGISTNO
AND t1.CLAIMNO = t3.CLAIMNO
AND t3.ACCIDENTNO = t4.ACCIDENTNO
AND t1.CLAIMNO = t6.CLAIMNO
AND t1.REGISTNO = t7.REGISTNO
AND t3.ACCIDENTNO = t5.ACCIDENTNO
AND t3.ACCIDENTNO = t10.ACCIDENTNO (+)
AND t3.ACCIDENTNO = t11.ACCIDENTNO (+)
AND t1.POLICYNO = t8.POLICYNO
AND t1.CLAIMNO = t9.CLAIMNO
AND t1.claimdate between date'2012-01-01' and date'2012-03-28'
group by t2.POLICYNO,t8.STARTDATE,
t8.ENDDATE,
t1.damagestartdate,
t1.REGISTNO,
t1.REPORTDATE,
t1.CLAIMNO,
t3.ACCIDENTNO,
t1.CLAIMDATE,
t1.underwriteenddate ,
t1.CANCELDATE ,
t1.endcasedate ,
t1.INSUREDNAME ,
t1.CHNLDESC ,
t1.BUSINESSNATUREDESC ,
t1.AGENTNAME ,
t1.comcode ,
t1.comname ,
t1.riskcode ,
t1.riskname ,
t1.damagereasondesc ,
t1.sumclaim ,
t1.settledloss*t1.exchrate*t1.coinsrate/100 ,
t2.DAMAGEADDRESS ,
t4.SURVEYORID ,
t3.OPERATORID ,
t5.ASSESSERID ,
t10.ASSESSERID ,
t11.ASSESSERID ,
t5.VERIFIERID ,
t6.COMPENSATORID ,
t7.UNDERWRITEID ,
t9.ENDCASERID
order by t1.CLAIMNO