select(selectshortnamefromldcomwherecomcode=v.comcode)||'____',
(selectcodename
fromldcode
wherecodetype='salechnl'
andcode=v.code),
sum(cbcount),
nvl(sum(cbsumprem),0),
sum(wtcount),
nvl(sum(wtsumprem),0),
sum(ctcount),
nvl(sum(ctsumprem),0),
sum(xtcount),
nvl(sum(xtsumprem),0)
from(selectk.comcodecomcode,
k.shortname,
d.codeCODE,
d.codename,
(selectcount(distinctl.contno)
fromv_temp_bqcontl
wherel.optype='CB'
andl.managecomlikek.comcode||'%'
andopdatebetweento_date('2017-01-01','yyyy-mm-dd')andto_date('2017-07-27','yyyy-mm-dd')
andl.salechnl=D.CODE)cbcount,
(selectSUM(l.money)
fromv_temp_bqcontl
wherel.optype='CB'
andl.managecomlikek.comcode||'%'
andopdatebetweento_date('2017-01-01','yyyy-mm-dd')andto_date('2017-07-27','yyyy-mm-dd')
andl.salechnl=D.CODE)cbsumprem,
(selectcount(DISTINCTl.contno)
fromv_temp_bqcontl
wherel.optype='WT'
andl.managecomlikek.comcode||'%'
andopdatebetweento_date('2017-01-01','yyyy-mm-dd')andto_date('2017-07-27','yyyy-mm-dd')
andl.salechnl=D.CODE)wtcount,
-1*(selectsum(l.money)
fromv_temp_bqcontl
wherel.optype='WT'
andl.managecomlikek.comcode||'%'
andopdatebetweento_date('2017-01-01','yyyy-mm-dd')andto_date('2017-07-27','yyyy-mm-dd')
andl.salechnl=D.CODE)wtsumprem,
(selectcount(DISTINCTl.contno)
fromv_temp_bqcontl
wherel.optype='CT'
andl.managecomlikek.comcode||'%'
andopdatebetweento_date('2017-01-01','yyyy-mm-dd')andto_date('2017-07-27','yyyy-mm-dd')
andl.salechnl=D.CODE)ctcount,
-1*(selectsum(l.money)
fromv_temp_bqcontl
wherel.optype='CT'
andl.managecomlikek.comcode||'%'
andopdatebetweento_date('2017-01-01','yyyy-mm-dd')andto_date('2017-07-27','yyyy-mm-dd')
andl.salechnl=D.CODE)ctsumprem,
(selectCOUNT(DISTINCTl.contno)
fromv_temp_bqcontl
wherel.optypeIN('XT','YT')
andl.managecomlikek.comcode||'%'
andopdatebetweento_date('2017-01-01','yyyy-mm-dd')andto_date('2017-07-27','yyyy-mm-dd')
andl.salechnl=D.CODE)xtcount,
-1*(selectsum(l.money)
fromv_temp_bqcontl
wherel.optypeIN('XT','YT')
andl.managecomlikek.comcode||'%'
andopdatebetweento_date('2017-01-01','yyyy-mm-dd')andto_date('2017-07-27','yyyy-mm-dd')
andl.salechnl=D.CODE)xtsumprem
fromldcomk,ldcoded
whered.codetype='salechnl'
andk.comcodenotlike'8600%'
andk.comcodelike'86030201%'
andd.code='03'
andLENGTH(k.comcode)=8
orderbyk.comcode,d.code)v
groupbyROLLUP(v.comcode,v.code)
分 -->
|
