/*

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’cx..apye’) AND type in (N’U’))
DROP TABLE cx..apye
Create Table cx..apye (cDwCode nvarchar(20),cDwName nvarchar(120),cDeptCode nvarchar(20),cDepName nvarchar(120),cPerson nvarchar(20), cPersonName nvarchar(120), cInvCode nvarchar(20), cInvName nvarchar(120),cInvStd nvarchar(60),cDwCCode nvarchar(20),cDWCName nvarchar(120),cDwDCode nvarchar(20),cDCName nvarchar(120),cHDwCode nvarchar(20),cHDwName nvarchar(120),cHDptCode nvarchar(20),cHDepName nvarchar(120),cHPsnCode nvarchar(20),cHPersonName nvarchar(120),cInvCCode nvarchar(20),cInvCName nvarchar(120),cCode nvarchar(20),cCode_Name nvarchar(120),cItem_Class nvarchar(2),cItem_Name nvarchar(20),cItemCode nvarchar(20),cItemName nvarchar(60),cContractType nvarchar(12),cContractTypeName nvarchar(40),cContractID nvarchar(64),cContractName nvarchar(400),cCusCreditCompany nvarchar(20),cCusCreditName nvarchar(120),qc_f money,qc_s float,qc money,jf_f money,jf_s float,jf money,df_f money,df_s float,df money,jf_f2 money,jf_s2 float,jf2 money,df_f2 money,df_s2 float,df2 money,ye_f money,ye_s float,ye money,turnrate_f float,turnrate float,turndays_f float,turndays float,csysid nvarchar(2))

declare @rq as date,@rq2 as date
set @rq=’2021-10-01′
set @rq2=’2021-10-31′
truncate table apye
insert into cx..apye(cdwcode,cdwname,chdwcode,chdwname,ccode,ccode_name,qc_f,qc_s,qc,jf_f,jf_s,jf,df_f,df_s,df,csysid)
Select
max(cdwcode),max(cdwname) as cdwname,max(chdwcode),
max(case when vendor_1.cvenname<>” then vendor_1.cvenname else vendor_1.cvenabbname end),
max(a.ccode),max(code.ccode_name),
SUM(case when ((isnull(dcreditstart,dregdate)<@rq)) and a.cexch_name<>’人民币’ then iCAmount_f-iDAmount_f else 0 end) as qc_f,
SUM(case when ((isnull(dcreditstart,dregdate)<@rq)) then iCAmount_s-iDAmount_s else 0 end) as qc_s,
SUM(case when ((isnull(dcreditstart,dregdate)<@rq)) then iCAmount-iDAmount else 0 end) as qc,
SUM(case when ((isnull(dCreditStart,dregdate)>=@rq and isnull(dCreditStart,dregdate)<=@rq2)) and a.cexch_name<>’人民币’
then iCAmount_f else 0 end) as jf_f,
SUM(case when ((isnull(dCreditStart,dregdate)>=@rq and isnull(dCreditStart,dregdate)<=@rq2)) then iCAmount_s else 0 end)
as jf_s,
SUM(case when ((isnull(dCreditStart,dregdate)>=@rq and isnull(dCreditStart,dregdate)<=@rq2)) then iCAmount else 0 end) as jf,
SUM(case when ((isnull(dCreditStart,dregdate)>=@rq and isnull(dCreditStart,dregdate)<=@rq2)) and a.cexch_name<>’人民币’
then iDAmount_f else 0 end) as df_f,
SUM(case when ((isnull(dCreditStart,dregdate)>=@rq and isnull(dCreditStart,dregdate)<=@rq2)) then iDAmount_s else 0 end)
as df_s,
SUM(case when ((isnull(dCreditStart,dregdate)>=@rq and isnull(dCreditStart,dregdate)<=@rq2)) then iDAmount else 0 end)
as df,
‘AP’ From Ap_DetailVend_s a with (nolock)
LEFT JOIN Vendor as Vendor_1 with (nolock) ON a.chdwcode=Vendor_1.cVenCode
LEFT JOIN code with (nolock) ON a.cCode=code.cCode where ((isnull(dcreditstart,dregdate)<=@rq2)) and a.iflag<=2
Group by cdwcode,chdwcode

truncate table cx..apye2
Insert into cx..apye2(cdwcode,cdwname,chdwcode,chdwname,ccode,ccode_name,qc_f,qc_s,qc,jf_f,jf_s,jf,df_f,df_s,df,jf_f2,jf_s2,jf2,df_f2,df_s2,df2)
Select max(cdwcode),max(cdwname) as cdwname,max(chdwcode),max(chdwname),max(a.ccode),max(ccode_name),sum(qc_f),sum(qc_s),sum(qc),sum(jf_f),sum(jf_s),sum(jf),sum(df_f),sum(df_s),sum(df),sum(jf_f2),sum(jf_s2),sum(jf2),sum(df_f2),sum(df_s2),sum(df2) From cx..apye a Group by cdwcode,chdwcode

update cx..apye2 set jf_f=(case when jf_f is null then 0 else jf_f end),jf_s=(case when jf_s is null then 0 else jf_s end),jf=(case when jf is null then 0 else jf end),df_f=(case when df_f is null then 0 else df_f end),df_s=(case when df_s is null then 0 else df_s end),df=(case when df is null then 0 else df end),jf_f2=(case when jf_f2 is null then 0 else jf_f2 end),jf_s2=(case when jf_s2 is null then 0 else jf_s2 end),jf2=(case when jf2 is null then 0 else jf2 end),df_f2=(case when df_f2 is null then 0 else df_f2 end),df_s2=(case when df_s2 is null then 0 else df_s2 end),df2=(case when df2 is null then 0 else df2 end)

Update cx..apye2 set ye_f=qc_f+jf_f-df_f-jf_f2+df_f2,ye=qc+jf-df-jf2+df2

Delete cx..apye2 where qc=0 And jf=0 And df=0 And jf2=0 And df2=0

Update cx..apye2 set turnrate_f=round((case when (qc_f+ye_f)=0 then 0 else (jf_f+df_f2)/((qc_f+ye_f)/2) end),2),
turnrate=round((case when qc+ye=0 then 0 else (jf+df2)/((qc+ye)/2) end),2)

Update cx..apye2 set turndays_f=(case when isnull(turnrate_f,0)=0 then 0 else (11-11+1)*30/turnrate_f end),turndays=(case when isnull(turnrate,0)=0 then 0 else (11-11+1)*30/turnrate end)

–数据保存在apye2
–数据另存为apye3