金蝶WISE销售发票勾稽记录SQL

最新推荐文章于 2023-12-04 13:46:46 发布

kuyz

最新推荐文章于 2023-12-04 13:46:46 发布

阅读量641

点赞数

分类专栏： sql 文章标签： java docker 金蝶

本文链接：https://blog.youkuaiyun.com/kuyz1/article/details/121621723

版权

sql 专栏收录该内容

9 篇文章

订阅专栏

这段代码创建了一个名为#TmpCheckInfo的临时表，包含多种财务检查信息字段，并设置了默认值。接着创建了两个索引以优化查询性能。随后，通过复杂的SQL插入语句，将数据从多个表中聚合并插入到#TmpCheckInfo表中，涉及了资金流转、检查类型、人员、部门等多个维度的数据。最后，选择了特定条件的数据并显示所有信息，然后删除#TmpCheckInfo表。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Create Table #TmpCheckInfo(
FNumber varchar(255) default(' '),
FRP  int default(1),
FSortID  int default(-1000),
FDate datetime,
FID  int ,
FCheckID  int default(0),
FCheckManerID int default(0),
FCheckTypeID int default(0),
FCheckManer varchar(255) default(' '),
FCheckType varchar(255) default(' '),
FTransfer int default(0),
FTransferID int default(0),
FChecker int default(0),
FCurrencyID int default(0),
FExchangeRate decimal(28,10) default(0),
FVoucherID int default(0),
FARtoAR int default(0),
FPreToPre int default(0),
FItemClassID int default(0),
FUnTread int default(0),
FUnEndorse tinyint default(0),
FUnTreadID int default(0),
FType int default(0),
FISinit int default(0),
FInvoiceID int default(0),
FRPBillID int default(0),
FBillID int default(0),
FPre int default(0),
FK3Import int default(0),
FBegID int default(0),
FContactID int default(0),
FBadID int default(0),
FCheckdate dateTime ,
FExplanation varchar(255) default(' '),
FNote varchar(255) default(' '),
FCustomerName varchar(255) default(' '),
FCustomerNumber varchar(255) default(' '),
FDepartmentName varchar(255) default(' '),
FDepartmentNumber varchar(255) default(' '),
FEmployeeName varchar(255) default(' '),
FEmployeeNumber varchar(255) default(' '),
FCurrencyName varchar(255) default(' '),
FCheckUser varchar(255) default(' '),
FBillAmountFor money default(0),
FBillRemainAmountFor money default(0),
FCheckAmount1 money default(0),
FCheckAmountFor1 money default(0),
FCheckAmount2 money default(0),
FCheckAmountFor2 money default(0),
FRemainAmount money default(0),
FRemainAmountFor money default(0),
FContractNo varchar(255) default(' '),
FOrderNo varchar(255) default(' '),
FCheckQty1 decimal(28,10) default(0),
FCheckQty2 decimal(28,10) default(0),
FRemainQty decimal(28,10) default(0),
FItemNumber varchar(255) default(' '),
FItemName varchar(255) default(' '),
FModel varchar(255) default(' '),
FUnitName varchar(255) default(' '),
FFincDate datetime)
CREATE INDEX idx_TmpCheckInfo_FType ON #TmpCheckInfo (FType)
CREATE INDEX idx_TmpCheckInfo_FType1 ON #TmpCheckInfo (FDate,FNumber)


 insert into #TmpCheckInfo(
FBadID,FRP,FNote,FBegID,FRpBillID,FBillID,FInvoiceID,Fk3Import,FNumber,FDate,FPre,
FCheckTypeID,FCheckManerID,FCheckID,FTransfer,FTransferID,FChecker,FCurrencyID,FExchangeRate,
FVoucheriD,FARtoAR,FPreToPre,FItemClassID,FUnEndorse,FUnTread,FUnTreadID,FType,FISinit,
FContactID,FCheckDate,FExplanation,FCustomerName,FCustomerNumber,FDepartmentName,
FDepartmentNumber, FEmployeeName,FEmployeeNumber, FCurrencyName, FCheckUser,
FBillAmountFor,  FBillRemainAmountfor,
FCheckAmount1 , FCheckAmountFor1, a.FRemainAmount, a.FRemainAmountFor,
FContractNo,FOrderNo,FCheckQty1,FRemainQty,FItemNumber,FItemName,FModel,FUnitName,FFincDate)
 select a.FBadID,a.FRP,a.FExplanation FNote,b.FBegID,b.FRpBillID,b.FBillID,b.FInvoiceID,b.Fk3Import,b.FNumber,b.FDate,b.FPre,a.FCheckType,a.FCheckManer,a.FID FCheckID,a.FTransfer,a.FTransferID,a.FChecker,a.FCurrencyID,a.FExchangeRate,
 a.FVoucheriD,a.FARtoAR,a.FPreToPre,a.FItemClassID,a.FUnEndorse,a.FUnTread,a.FUnTreadID,a.FType,a.FISinit,
 a.FContactID,a.FCheckDate,b.FExplanation,
 i.FName FCustomerName,i.FNumber FCustomerNumber,
 ii.FName FDepartmentName,ii.FNumber FDepartmentNumber,
 iii.FName FEmployeeName,iii.FNumber FEmployeeNumber,
 cur.FName FCurrencyName,u.Fname FCheckUser,
 b.FAmountFor FBillAmountfor, b.FRemainAmountFor FBillRemainAmountfor, a.FCheckAmount , a.FCheckAmountFor, a.FRemainAmount, a.FRemainAmountFor,
 ''FContractNo,''FOrderNo,0 FCheckQty,0 FRemainQty,''FItemNumber,''FItemName,''FModel,''FUnitName,b.FFincDate
 from t_rp_newcheckinfo a
 join t_rp_contact b on a.FContactID=b.FID
 left join t_item i on a.FCustomer=i.FItemID left join t_item ii on a.Fdepartment=ii.FItemID
 left join t_item iii on a.Femployee=iii.FItemID
 left join t_currency cur on a.FCurrencyID=cur.FCurrencyID
 left join t_User u on  a.FChecker=u.FUserID 
 where a.FRP=1 and a.FIsBad=0  and a.FCheckDate >= '1900-01-01' and a.FCheckDate<='9999-01-01' and b.FDate >= '1900-01-01' and b.FDate<='9999-01-01' and a.FID=12345 and(a.FCheckManer=1 or (a.FCheckType>0 and a.FCheckManer=3))
 union all 
 select a.FBadID,a.FRP,a.FExplanation FNote,b.FBegID,b.FRpBillID,b.FBillID,b.FInvoiceID,b.Fk3Import,b.FNumber,b.FDate,b.FPre,a.FCheckType,a.FCheckManer,a.FID FCheckID,a.FTransfer,a.FTransferID,a.FChecker,a.FCurrencyID,a.FExchangeRate,
 a.FVoucheriD,a.FARtoAR,a.FPreToPre,a.FItemClassID,a.FUnEndorse,a.FUnTread,a.FUnTreadID,a.FType,a.FISinit,
 a.FContactID,a.FCheckDate,b.FExplanation,
 i.FName FCustomerName,i.FNumber FCustomerNumber,
 ii.FName FDepartmentName,ii.FNumber FDepartmentNumber,
 iii.FName FEmployeeName,iii.FNumber FEmployeeNumber,
 cur.FName FCurrencyName,u.Fname FCheckUser,
 b.FAmountFor FBillAmountfor, b.FRemainAmountFor FBillRemainAmountfor, c.FCheckAmount,c.FCheckAmountFor,
 (case when c.FCheckAmount<0 then x.FRemainAmount else y.FRemainAmount end)FRemainAmount,
 (case when c.FCheckAmount<0 then x.FRemainAmountFor else y.FRemainAmountFor end)FRemainAmountFor, 
 c.FContractNo,c.FOrderNo,c.FCheckQty, (case when c.FCheckAmount<0 then x.FRemainQuantity else y.FRemainQuantity end)FRemainQty,
 p.FNumber FItemNumber,p.FName FItemName,p.FModel,q.FName FUnitName,b.FFincDate
 from (select FInterID,FID,FBillID,FType,FIsinit,FEntryID,FItemID,FUnitID,FContractNo,FOrderNo,sum(FCheckQuantity)FCheckQty,sum(FRemainQuantity)FRemainQty, sum(FCheckAmount)FCheckAmount,sum(FCheckAmountFor)FCheckAmountFor, sum(FRemainAmount)FRemainAmount,sum(FRemainAmountFor)FRemainAmountFor from t_rp_checkEntry group by FInterID,FID,FBillID,FType,FIsinit,FEntryID,FItemID,FUnitID,FContractNo,FOrderNo)c
 left join (select FInterID,FBillID,FType,FIsinit,FEntryID,Max(FRemainQuantity) FRemainQuantity,Max(FRemainAmount) FRemainAmount,Max(FRemainAmountFor) FRemainAmountFor
 from t_rp_checkEntry where FCheckAmountFor<0 group by FInterID,FBillID,FType,FIsinit,FEntryID)x on c.FInterID=x.FInterID
 and c.FBillID=x.FBillID and c.FType=x.FType and c.FIsinit=x.FIsinit and c.FEntryID=x.FEntryID
 left join (select FInterID,FBillID,FType,FIsinit,FEntryID,Min(FRemainQuantity)FRemainQuantity,Min(FRemainAmount) FRemainAmount,Min(FRemainAmountFor) FRemainAmountFor
 from t_rp_checkEntry where FCheckAmountFor>=0 group by FInterID,FBillID,FType,FIsinit,FEntryID)y on c.FInterID=y.FInterID
 and c.FBillID=y.FBillID and c.FType=y.FType and c.FIsinit=y.FIsinit and c.FEntryID=y.FEntryID
 join t_rp_newcheckinfo a on c.FInterID=a.FInterID
 join t_rp_contact b on a.FContactID=b.FID
 left join t_item i on a.FCustomer=i.FItemID left join t_item ii on a.Fdepartment=ii.FItemID
 left join t_item iii on a.Femployee=iii.FItemID
 left join t_currency cur on a.FCurrencyID=cur.FCurrencyID
 left join t_User u on  a.FChecker=u.FUserID 
 left join t_icItem p on c.FItemID=p.FItemID left join t_MeasureUnit q on c.FUnitID=q.FMeasureUnitID 
 where a.FRP=1 and a.FIsBad=0  and a.FCheckDate >= '1900-01-01' and a.FCheckDate<='9999-01-01' and b.FDate >= '1900-01-01' and b.FDate<='9999-01-01'  and a.FCheckManer=2
 union all 
 select a.FBadID,a.FRP,a.FExplanation FNote,b.FBegID,b.FRpBillID,b.FBillID,b.FInvoiceID,b.Fk3Import,b.FNumber,b.FDate,b.FPre,a.FCheckType,a.FCheckManer,a.FID FCheckID,a.FTransfer,a.FTransferID,a.FChecker,a.FCurrencyID,a.FExchangeRate,
 a.FVoucheriD,a.FARtoAR,a.FPreToPre,a.FItemClassID,a.FUnEndorse,a.FUnTread,a.FUnTreadID,a.FType,a.FISinit,
 a.FContactID,a.FCheckDate,b.FExplanation,
 i.FName FCustomerName,i.FNumber FCustomerNumber,
 ii.FName FDepartmentName,ii.FNumber FDepartmentNumber,
 iii.FName FEmployeeName,iii.FNumber FEmployeeNumber,
 cur.FName FCurrencyName,u.Fname FCheckUser,
 b.FAmountFor FBillAmountfor, b.FRemainAmountFor FBillRemainAmountfor, c.FCheckAmount,c.FCheckAmountFor,c.FRemainAmount,c.FRemainAmountFor,
 c.FContractNo,c.FOrderNo,c.FCheckQuantity FCheckQty ,c.FRemainQuantity FRemainQty,p.FNumber FItemNumber,p.FName FItemName,p.FModel,q.FName FUnitName,b.FFincDate
 from t_rp_checkEntry c
 join t_rp_newcheckinfo a on c.FInterID=a.FInterID
 join t_rp_contact b on a.FContactID=b.FID
 left join t_item i on a.FCustomer=i.FItemID left join t_item ii on a.Fdepartment=ii.FItemID
 left join t_item iii on a.Femployee=iii.FItemID
 left join t_currency cur on a.FCurrencyID=cur.FCurrencyID
 left join t_User u on  a.FChecker=u.FUserID 
 left join t_icItem p on c.FItemID=p.FItemID left join t_MeasureUnit q on c.FUnitID=q.FMeasureUnitID 
 where a.FRP=1 and a.FIsBad=0  and a.FCheckDate >= '1900-01-01' and a.FCheckDate<='9999-01-01' and b.FDate >= '1900-01-01' and b.FDate<='9999-01-01'  and a.FCheckManer=3 and a.FCheckType=0
 select *From #TmpCheckInfo
 drop table #TmpCheckInfo