烟草及IT服务流程平台SQL等，it服务流程sql

烟草及IT服务流程平台SQL等，it服务流程sql

==========公积金网贷启动程序==========

/usr/java/jdk1.6/bin/java PTNRdecryptFromCNCB 2 123456 PTNRtestNew.key CNCBtestNew.cer 22.60.90.20 8001

/usr/java/jdk1.6/bin/java PTNRdecryptFromCNCBThread 2 123456 PTNRtestNew.key CNCBtestNew.cer 22.60.90.20 8001

=============烟草查询============

select * from ibscpysig
--012900044580001  015200044580001  015300044580001

select * from ibsjnl 

where (std400dwcd = '012900044580001' or std400dwcd = '015200044580001' or std400dwcd = '015300044580001') 

and ibslocdate = '2016-09-14' and std400cunm = '王震'


select * from ibscussig where (std400dwcd = '012900044580001' or std400dwcd = '015200044580001' or std400dwcd = '015300044580001') 
and std400cunm = '王震'


select std400idno, std400cunm, ibscrtdate, stdpriacno, stdopntrno
from ibscussig where (std400dwcd = '012900044580001' or std400dwcd = '015200044580001' or std400dwcd = '015300044580001') 
and ibscrtdate >= '2016-08-08' 
order by ibscrtdate

===============供热及水务情况================

供热情况：
select * from ibsjnl where (std400dwcd = '012990020500006' or std400dwcd = '012990020510005') and ibslocdate >= '2016-10-01'

水务情况：
select * from ibsjnl where std400dwcd = '012990049000012' and ibslocdate >= '2016-10-01'

==============烟草客户统计需求===============

------1,基本信息
select ibscrtdate as 签约日期, std400cunm as 姓名, stdpriacno as 卡号, stdopntrno as 支行名称, stdrestrno as 推荐人号, std400idno as 身份证号, ibscustnum as 商户号
from ibscussig where (std400dwcd = '012900044580001' or std400dwcd = '015200044580001' or std400dwcd = '015300044580001')
order by ibscrtdate 


select * from ibsjnl where (std400dwcd = '012900044580001' or std400dwcd = '015200044580001' or std400dwcd = '015300044580001')


-------2,流水信息（不用了）
select a.ibscustnum as 商户号, a.std400dwcd as 单位编号, a.stdpriacno as 卡号, b.std400cunm as 姓名, a.stdtranamt as 交易金额, a.ibslocdate as 交易日期 from
(
select ibscustnum, std400dwcd, stdpriacno, stdtranamt, ibslocdate from ibsjnl
where ibscustnum
in (select ibscustnum from ibscussig where (std400dwcd = '012900044580001' or std400dwcd = '015200044580001' or std400dwcd = '015300044580001') and ibscrtdate >= '2016-11-21')
and std400gsst = '0'
) a
left join ibscussig b
on a.stdpriacno = b.stdpriacno and a.std400dwcd = b.std400dwcd
order by a.ibscustnum, a.ibslocdate


--------去除空姓名
select * from ycls

insert into yclstmp (select distinct shh, xm from ycls where xm is not null)

merge into ycls as a 
        using (select shh, xm from yclstmp) as b
        on a.shh = b.shh
        when matched then
                        update set a.xm = b.xm
        else
                        ignore;

------3,联动收益    xhxdb   test.yckh   （存款时点日均，要这个人所有账户的和及本烟草账户）

select * from db2inst1.jaf_om_organization

select substr(zh, 1, 5) from test.yckh

select a.qyrq as 签约日期, a.xm as 姓名, a.kh as 卡号, b.orgname as 支行, a.tjr as 推荐柜员, a.sfz as 身份证, a.shh as 商户号, d.aum_bal as 管理资产余额, e.cur_cd as 币种代码,
sum(e.acct_bal) as 存款时点余额, sum(e.y_dpst_acct_bal_accm/365) as 存款日均余额, f.agmt_id as 协议编号, g.cur_cd as 币种代码, g.acct_bal as 本卡时点余额, 
g.y_dpst_acct_bal_accm/365 as 本卡存款日均余额
from test.yckh a
left join db2inst1.jaf_om_organization b on b.orgcode = substr(a.zh, 1, 5)
left join edw.BHIF_INDV_CUST_BASIC_INFO c on a.sfz = c.cert_num
left join edw.BHIF_INDV_CUST_AUM d on d.cust_id = c.cust_id
left join edw.BHIF_DPST_ACCT_BAL e on e.core_cust_id = c.cust_id
left join edw.BHIF_INDV_CURR_DPST_ACCT_ATTR f on f.pri_cust_acct_num = a.kh
left join edw.BHIF_DPST_ACCT_BAL g on g.agmt_id = f.agmt_id
group by a.qyrq, a.xm, a.kh, b.orgname, a.tjr, a.sfz, a.shh, d.aum_bal, e.cur_cd, f.agmt_id, g.cur_cd, g.acct_bal, g.y_dpst_acct_bal_accm/365

select * from edw.BHIF_INDV_CUST_BASIC_INFO where cert_num = '210106198601025828'

select * from edw.BHIF_INDV_CUST_AUM

select * from edw.BHIF_DPST_ACCT_BAL 

select * from edw.BHIF_INDV_CURR_DPST_ACCT_ATTR where pri_cust_acct_num = '6217682900554578'

-----4，烟草客户最近交易日期及历史交易金额(主要在左连接的部分在on上加限制条件，而不是在where上加限制条件)

select * from 
(
select a.签约日期, a.姓名, a.卡号, a.记录状态, a.支行名称, a.推荐人号, a.身份证号, a.商户号, sum(b.stdtranamt) as 金额, max(b.ibslocdate) as 日期 from
(select ibscrtdate as 签约日期, std400cunm as 姓名, stdpriacno as 卡号, std400stcd as 记录状态, stdopntrno as 支行名称, stdrestrno as 推荐人号, std400idno as 身份证号, ibscustnum as 商户号, std400dwcd
from ibscussig where (std400dwcd = '012900044580001' or std400dwcd = '015200044580001' or std400dwcd = '015300044580001')
order by ibscrtdate) a
left join ibsjnl b
on a.卡号 = b.stdpriacno and a.std400dwcd = b.std400dwcd and b.std400gsst = '0'
group by a.签约日期, a.姓名, a.卡号, a.记录状态, a.支行名称, a.推荐人号, a.身份证号, a.商户号
order by a.卡号 desc
)
where 日期 <'2017-01-01'


select ibscrtdate as 签约日期, std400cunm as 姓名, stdpriacno as 卡号, std400stcd as 记录状态, stdopntrno as 支行名称, stdrestrno as 推荐人号, std400idno as 身份证号, ibscustnum as 商户号, std400dwcd
from ibscussig where (std400dwcd = '012900044580001' or std400dwcd = '015200044580001' or std400dwcd = '015300044580001')
order by ibscrtdate

--------流水信息

select a.签约日期, a.姓名, a.卡号, a.记录状态, a.支行名称, a.推荐人号, a.身份证号, a.商户号, b.stdtranamt as 金额, b.ibslocdate as 日期 from
(select ibscrtdate as 签约日期, std400cunm as 姓名, stdpriacno as 卡号, std400stcd as 记录状态, stdopntrno as 支行名称, stdrestrno as 推荐人号, std400idno as 身份证号, ibscustnum as 商户号, std400dwcd
from ibscussig where (std400dwcd = '012900044580001' or std400dwcd = '015200044580001' or std400dwcd = '015300044580001')
order by ibscrtdate) a
left join ibsjnl b
on a.卡号 = b.stdpriacno and a.std400dwcd = b.std400dwcd and b.ibslocdate >= '2017-01-01' and b.ibslocdate <= '2017-02-28' and b.std400gsst = '0'
order by a.卡号, b.ibslocdate desc

--------支行名称处理

select a.qyrq as 签约日期, a.xm as 姓名, a.kh as 卡号, a.zt as 状态, b.orgname as 支行, a.tjr as 推荐柜员, a.sfz as 身份证, a.shh as 商户号, a.je as 交易金额, a.jyrq as 最近一次交易日期
from test.newyckh a 
left join db2inst1.jaf_om_organization b on b.orgcode = substr(a.zh, 1, 5)

--------处理客户号

select a.qyrq as 签约日期, a.xm as 姓名, a.kh as 卡号, a.zt as 状态, b.orgname as 支行, a.tjr as 推荐柜员, a.sfz as 身份证, a.shh as 商户号, a.je as 交易金额, a.jyrq as 最近一次交易日期,
c.cust_id as 客户号
from test.newyckh a 
left join db2inst1.jaf_om_organization b on b.orgcode = substr(a.zh, 1, 5)
left join edw.bhif_indv_cust_basic_info c on c.cert_num = a.sfz

--------信用卡客户号信息

select a.qyrq as 签约日期, a.xm as 姓名, a.kh as 卡号, a.zt as 状态, b.orgname as 支行, a.tjr as 推荐柜员, a.sfz as 身份证, a.shh as 商户号, a.je as 交易金额, a.jyrq as 最近一次交易日期,
c.core_cust_id as 客户号
from test.newyckh a 
left join db2inst1.jaf_om_organization b on b.orgcode = substr(a.zh, 1, 5)
left join edw.BHIF_CRDT_CARD_CUST_BASIC c on c.cert_num = a.sfz

================公积金网贷查询===================

select a.id, a.cert_num, b.std400mobl from
(
select a.id, b.cert_num from xdw a
left join bhif_indv_cust_basic_info b
on a.name = b.cust_id
order by a.id
) a
left join gjjsq b
on a.cert_num = b.stdmerno
order by a.id

================IT服务平台查数需求=================

select * from edw.BHIF_INDV_DMND_ACCT_FLOW where cust_acct = '6217682900475824'

select * from edw.BHIF_INDV_CUST_BASIC_INFO where Cert_Num = '210104195707013124'

select * from edw.BHIF_INDV_CURR_DPST_ACCT_ATTR where core_cust_id = '001126859858'

select * from cibdtaxx.scsaa where sascno = '6217732901261531'

通过账号查询活期流水：
select * from cibdtaxx.dsdsa where dsacno = '7221110192007857626'

select * from cibhsy.dsdsa where dsacno = '7221110192007857626'

select * from cibdtaxx.scsaa where sacuno = '26859858'

7222110192006166616
7221110192007857626

select * from cibdtaxx.cisia where siidno = '210104195707013124'

select * from cibdtaxx.cisia where siidnm='王琪'
select * from cibdtaxx.cisia where siidno='210114196303111227'
select * from cibdtaxx.cicaa where cacuno='35042964'
select * from cibdtaxx.scsaa where sascno='4427302900111334'

select * from cibdtaxx.dddsa where ddacno='7221010182300115912'
select * from cibdtaxx.pmtxa where txtrcd='3806'
对手信息：
select * from cibhsy.dsdsa as a 
left join cibhsy.bkota as b  on a.dstrno=b.ottrno and a.dstrdt=b.ottrdt and a.dsacno=b.otacno   where dsacno='7214310182600003855' and dstrdt>='20010209' and dstrdt<='20061231'
select * from cibdtaxx.dsdsa as a 
left join cibdtaxx.bkota as b  on a.dstrno=b.ottrno and a.dstrdt=b.ottrdt and a.dsacno=b.otacno   where dsacno='7221010182600115912'
and dstrdt>='20140101'

=====================零售客户  交叉产品===================

select a.cust_id as 客户号, b.cust_cn_nm as 客户姓名, b.chk_mobl_num as 客户手机号, a.aum_bal as 资产余额,
c.Wbank_Cust_Typ_Cd as 网银客户类型代码, c.Mobl_Bank_Contr_Ind as 手机银行签约标志, c.Tbank_Contr_Ind as 电话银行签约标志,
c.SMS_Contr_Ind as 短信通签约标志, c.FX_Cust_Contr_Ind as 外汇客户签约标志, c.Payroll_Agen_Contr_Ind as 代发工资签约标志,
c.Cert_Wbank_Contr_Ind as 证书版网银签约标志, c.XJB_Contr_Ind as 薪金煲签约标志, c.Debt_Crdt_Rltv_Contr_Ind as 借贷关联签约标志,
d.Curr_Dpst_Cust_Ind as 活期存款客户标志, d.Tm_Dpst_Cust_Ind as 定期存款客户标志, d.Fund_Cust_Ind as 纯基金客户标志,
d.Fund_Fix_Amt_Invst_Cust_Ind as 基金定投客户标志, d.Insu_Cust_Ind as 保险客户标志, d.Pay_Prd_Insu_Cust_Ind as 期缴保险客户标志,
d.ST_Cust_Ind as 三方存管客户标志, d.Prec_Metal_Cust_Ind as 贵金属客户标志, d.Crdt_Card_Cust_Ind as 信用卡客户标志,
d.Debit_Card_Cust_Ind as 借记卡客户标志, d.X_Card_Cust_Ind as 香卡客户标志, d.XFNH_Card_Cust_Ind as 信福年华卡客户标志,
d.QCHL_Card_Cust_Ind as 七彩华龄卡客户标志, d.Home_Card_Cust_Ind as Home卡客户标志, d.YF_Card_Cust_Ind as 裕福卡客户标志,
d.Gold_Card_Cust_Ind as 金卡客户标志, d.Pltn_Card_Cust_Ind as 白金卡客户标志, d.Blk_Card_Cust_Ind as 黑金卡客户标志,
d.Dmd_Card_Cust_Ind as 钻石卡客户标志, d.Chrem_Cust_Ind as 理财客户标志, d.Tbond_Cust_Ind as 国债客户标志,
d.XJB_Cust_Ind as 薪金煲客户标志, d.Loan_Cust_Ind as 贷款客户标志, d.Mortg_Loan_Cust_Ind as 房抵贷客户标志,
d.CDB_Cust_Ind as 存贷宝客户标志, d.Fund_XJB_Cust_Ind as 薪金煲基金客户标志, d.Spec_Debit_Card_Cust_Ind as 指定借记卡客户标志,
d.Larg_Amt_Cret_Dpst_Cust_Ind as 大额存单客户标志, d.JY_Card_Cust_Ind as 菁英卡客户标志, d.OsFl_Fin_Cust_Ind as 出国金融客户标志,
d.Invld_Cust_Ind as 无效客户标志
from BHIF_INDV_CUST_AUM a
left join BHIF_INDV_CUST_BASIC_INFO b on a.cust_id = b.cust_id
left join BHIF_INDV_CUST_CONTR_IND c on a.cust_id = c.cust_id
left join BHIF_INDV_CUST_HOLD_PROD_IND d on a.cust_id = d.cust_id
where a.aum_bal >= 10000 and a.aum_bal <= 50000 and b.open_cust_org_id = '722311'