非正常户认定期间仍然开票户数（负向）.docx
非正常户认定期间仍然开票户数（负向）-非正常户认定期间仍然开票户数（负向）-创建临时表CREATETAB1.E1.S_TSZB_Z019_FPXX(XfnsrdzdahVARCHAR2(40),KPRQDATE,JENUMBER,FSDMVARCHAR2(100)zYXQQDATE,YXQZDATE)NO1.OGGING;CREATETAB1.E1.S_TSZB_Z019_NSR(Nsrdzdahvarchar2(40)notnull,YXQQDATE,YXQZDATE)NO1.OGGING;计算纳税人清单INSERT/*+append*/INTO1.S_TSZB_Z019_NSR(NSRDZDAH,YXQQ,YXQZ)selectm.nsrdzdah,m.YXQQ+n.lxtsYXQQ,m.YXQZfrom(SE1.ECTDISTINCTP.NSRDZDAHzTRNC(A.RDRQ)YXQQ,Trunc(Ajcfzchrq)-1yxqzFROMWD_NSRXXP,DJ_FZCHRDXXAwherep.Nsrdzdah=a.nsrdzdahANDP.NSRZT_IDIN(,03,04,z,05,06,13,)ANDP.KZZTDJ1.X_ID<,1140,ANDNV1.(P.ZFJG1.XJD,'O')NOTIN(,2,z,3')ANDRYXBZ='Y,ANDNV1.(P.KQCCSZTDJBZ,N,)='N'andp.nsrztlxjdisnullandp.dsswjg_dm=dsswjg_dmANDtrunc(AJCFZCHRQ)>=TRUNC(fxqjzJYYYY,)分析期间止，参数为日期型，格式:FXQJZJDBCTYPE=TIMESTAMPANDtrunc(A.JCFZCHRQ)<=TRUNC(fxqjz)andTrunc(Ajcfzchrq)>=trunc(a.rdrq)UNIONA1.1.SE1.ECTRNSRDZDAH,TRNC(MAX(A.RDRQ)YXQQ,DATE,9999-12-31,YXQZFROMWD_NSRXXP,DJ_FZCHRDXXAwherep.Nsrdzdah=a.nsrdzdahandRNSRZTJDIN(,03,z,04,O5,'06,z'13')ANDP.KZZTDJ1.X_ID<'1140'ANDNV1.(P.ZFJG1.XJDx,0,)NOTIN(,2,3')ANDRYXBZ='Y'ANDNV1.(P.KQCCSZTDJBZ,N,)='N'andp.nsrztlxjdisnullandp.dsswjg_dm=dsswjg_dmand(Ajcfzchrqisnullorajcfzchrq>=trunc(sysdae+1)ANDTRUNC(A.RDRQ)<=TRUNC(fxqjz)分析期间止，参数为日期型，格式：-FXJQZGROUPBYP.NSRDZDAH)minnerjoin(selectnn.nsrdzdahznn.yxqq,nn.yxqz,casewhenCEI1.(nvl(nn.1.XKPSX,0)24)<0thenOwhenCEI1.(nvl(nn.1.XKPSX,0)24)>10then10elseCEI1.(nvl(nn.1.XKPSX,0)24)endasIxtsfrom(selecta.nsrdzdah,a.yxqq,a.yxqz,a.1.XKPSX,row-number()over(partitionbynsrdzdahorderbya.1.XKPSXdesc)pxfromFP_PZHDXXa,WD_FPZ1.bwherea.FPZ1._ID=b.FPZ1._IDandb.ZPPZ_IDnotin(,0,4'z,8,)andYXQZ>YXQQANDtrunc(YXQQ)<=fxqjzandtrunc(YXQZ)>=fxqjz)nnwherepx=l)nonm.nsrdzdah=n.nsrdzdah;commit;一计算发票数据INSERT/*+APPEND*/INTO1.S_TSZB_Z019_FPXX(XFNSRDZDAH,KPRQ,JE,FSDM,YXQQ,YXQZ)Selecttxfnsrdzdah,t.kprq,tje,t.fpdmt.fphm11t.kpyffsdm,nsr.yxqq,nsr.yxqzFROMDZDZ_DZDZ_FPXX_ZZSFPTINNERJOIN1.S_TSZB_Z019_NSRNSRont.xfnsrdzdah=nsr.nsrdzdahANDtrunc(T.KPRQ)>=trunc(NSR.YXQQ)ANDtrunc(T.KPRQ)<=trunc(NSR.YXQZ)WHERET.KPYF>=TO_CHAR(TRUNC(fxqjzz,YYYY,),YYYYMM,)-FXJQZANDT.KPYF<=TO_CHAR(fxqjz,YYYYMM')-FXJQZANDT.FPZT-BZIN(,O,z,l,)and(TSPZ-DM<>,02,orTSPZ_DMisnull);COMMIT;INSERT/*+APPEND*/INTO1.S_TSZB_Z019_FPXX(XFNSRDZDAH,KPRQ,JE,FSDMzYXQQ,YXQZ)Selecttxfnsrdzdah,t.kprq,tjezt.fpdmt.fphm11t.kpyffsdmznsr.yxqq,nsr.yxqzFROMDZDZ_DZDZ_FPXX_PTFPTInnerjoinls_tszb_zoi9_nsrnsront.xfnsrdzdah=nsr.nsrdzdahANDtrunc(T.KPRQ)>=trunc(NSR.YXQQ)ANDtrunc(T.KPRQ)<=trunc(NSR.YXQZ)WHERET.KPYF>=TO_CHAR(TRUNC(fxqjz,YYYY"),'YYYYMM')-FXJQZANDT.KPYF<=TO_CHAR(fxqjz,YYYYMM,)-FXJQZANDT.FPZT_BZIN(,0,z,1,)and(TSPZ-DM<>,02,orTSPZ_DMisnull);COMMIT;INSERT/*+APPEND*/INTO1.S_TSZB_Z019_FPXX(XFNSRDZDAH,KPRQ,JE,FSDM,YXQQ,YXQZ)Selecttxfnsrdzdah,t.kprq,tje,t.fpdmt.fphm11t.kpyffsdm,nsr.yxqq,nsr.yxqzFROMDZDZ_DZDZ_FPXX_DZZPTInnerjoinls_tszb_zoi9_nsrnsrONT.XFNSRDZDAH=NSR.NSRDZDAHANDtrunc(T.KPRQ)>=trunc(NSR.YXQQ)ANDtrunc(T.KPRQ)<=trunc(NSR.YXQZ)WHERET.KPYF>=TO_CHAR(TRUNC(fxqjz,YYYY,),YYYYMM,)-FXJQZANDT.KPYF<=TO_CHAR(fxqjz,YYYYMM')-FXJQZANDT.FPZT-BZIN(,O,z,l,)and(TSPZ-DM<>,02,orTSPZ_DMisnull);COMMIT;INSERT/*+APPEND*/INTO1.S_TSZB_Z019_FPXX(XFNSRDZDAH,KPRQ,JE,FSDMzYXQQ,YXQZ)Selecttxfnsrdzdah,t.kprq,tjezt.fpdmt.fphm11t.kpyffsdm,nsr.yxqq,nsr.yxqzFROMDZDZ_DZDZ_FPXX_DZFPTInnerjoinls_tszb_zoi9_nsrnsrONT.XFNSRDZDAH=NSR.NSRDZDAHANDtrunc(T.KPRQ)>=trunc(NSR.YXQQ)ANDtrunc(T.KPRQ)<=trunc(NSR.YXQZ)WHERET.KPYF>=TO_CHAR(TRUNC(fxqjz,YYYY"),'YYYYMM')-FXJQZANDT.KPYF<=TO_CHAR(fxqjz,YYYYMM,)-FXJQZANDT.FPZT_BZIN('0,z'1,)and(TSPZ-DM<>,02,orTSPZ_DMisnull);COMMIT;结果数据SE1.ECTDISTINCTRNSRMCas纳税人名称，nvl(P.SHXYDMzP.NSRSBH)as社会信用代码，p.nsrdzdahas纳税人电子档案号，RSWJGMCas主管税务机关，RSSG1.YSWRYMCas税收管理员，'非正常户YS纳税人状态，TO-CHAR(y.Y×QQ,'YYYY-MM-DD')as认定有效期起,TO_CHAR(y.YXQZ,1YYYY-MM-DD')as认定有效期止,TO_CHAR(fxqjz,'YYYY,)as年份,TFXQJZFSas开票份数，JEas开票金额，TO,CHAR(fxqjzf'YYYY-MM-DD,)as分析期间止，p.dsswjgmcas地市级税务机关，p.qxswjgmcas区县级税务机关from(Selectxfnsrdzdahnsrdzdah,YXQQ,YXQZ,SM(NV1.(JE,0)JE,COUNTfDISTINCTFSDM)FSFROM1.S_TSZB_Z019_FPXXFPXXGROUPBYXFNSRDZDAH,YXQQ,YXQZ)YWD_NSRXXPWHEREY.NSRDZDAH=P.NSRDZDAH
