T3V10_V11整理现存量存储过程分享

cqkjll · 发表于 2017-12-3 23:37:17

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

您需要登录才可以下载或查看，没有账号？注册账号

×

T3V10_V11整理现存量存储过程分享，好用就赞一个。SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE  PROCEDURE Pr_CleanStock
AS
Truncate Table CurrentStock
Drop Table TempCurrentStock
declare @startdate varchar(30)
set @startdate=(select top 1 left(convert(varchar(10),ddate,20),7)+'-01' from rdrecord where left(ccode,1)<>'-')
SELECT cWhCode,cInvCode,(CASE WHEN cFree1=''THEN NULL ELSE cFree1 END) AS cFree1 ,(CASE WHEN cFree2=''THEN NULL ELSE cFree2 END) AS cFree2 ,iBeginQuantity AS iQuantity, iBeginNum as iNum,  CAST(0 AS FLOAT) AS fOutQuantity,CAST(0 AS FLOAT) AS fOutNum,CAST(0 AS FLOAT) AS fInQuantity,CAST(0 AS FLOAT) AS fInNum INTO TempCurrentStock From ST_TotalAccount  Where iMonth=month(@startdate)
INSERT INTO TempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,iQuantity,iNum) SELECT  cWhCode, cInvCode,(CASE WHEN cFree1=''THEN NULL ELSE cFree1 END) AS cFree1 ,(CASE WHEN cFree2=''THEN NULL ELSE cFree2 END) AS cFree2 , (CASE WHEN bRdFlag<>0 THEN  CONVERT(DECIMAL(18,2),iQuantity)  ELSE -CONVERT(DECIMAL(18,2),iQuantity) end) as iQuantity, (CASE WHEN bRdFlag<>0 THEN CONVERT(DECIMAL(18,2),iNum)  ELSE -CONVERT(DECIMAL(18,2),iNum) END) AS iNum FROM RdRecord INNER JOIN RdRecords ON RdRecord.ID = RdRecords.ID WHERE  cVouchType<>'33'AND cVouchType<>'34'  AND RdRecord.dDate>=@startdate
INSERT INTO TempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,fOutQuantity,fOutNum) SELECT  EnDispatchs.cWhCode, EnDispatchs.cInvCode,(CASE WHEN EnDispatchs.cFree1='' THEN NULL ELSE EnDispatchs.cFree1 END) AS cFree1 ,(CASE WHEN EnDispatchs.cFree2='' THEN NULL ELSE EnDispatchs.cFree2 END) AS cFree2 , (ISNULL(iQuantity,0)-ISNULL(fOutQuantity,0)) AS fOutQuantity, (ISNULL(iNum,0)-ISNULL(fOutNum,0)) AS fOutNum  FROM EnDispatch INNER JOIN  EnDispatchs ON EnDispatch.EDID = EnDispatchs.EDID WHERE  ISNULL(EnDispatchs.cWhCode,'')<>''  AND ISNULL(EnDispatch.cSTCode,'')<>'' AND EnDispatchs.iQuantity>0 AND bReturnFlag=0 AND (CASE WHEN ISNULL(EnDispatchs.iNum,0) <>0 THEN (ISNULL(EnDispatchs.iQuantity,0)-ISNULL(EnDispatchs.fOutQuantity,0)) * (ISNULL(EnDispatchs.iNum,0)-ISNULL(EnDispatchs.fOutNum,0)) ELSE  (ISNULL(EnDispatchs.iQuantity,0)-ISNULL(EnDispatchs.fOutQuantity,0)) END) >0
INSERT INTO TempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,fOutQuantity,fOutNum) SELECT  DispatchLists.cWhCode as cWhCode, DispatchLists.cInvCode as cInvCode,(CASE WHEN DispatchLists.cFree1='' THEN NULL ELSE DispatchLists.cFree1 END) AS cFree1 , (CASE WHEN DispatchLists.cFree2='' THEN NULL ELSE DispatchLists.cFree2 END) AS cFree2 , (ISNULL(iQuantity,0)-ISNULL(fOutQuantity,0)) AS fOutQuantity, (ISNULL(iNum,0)-ISNULL(fOutNum,0)) AS fOutNum  FROM DispatchLists INNER JOIN  DispatchList ON DispatchLists.DLID = DispatchList.DLID    WHERE  DispatchList.cVouchType='05' AND ISNULL(DispatchLists.cWhCode,'')<>'' AND ISNULL(DispatchList.cSTCode,'')<>''  AND  (DispatchList.iSale=0 or DispatchList.iSale IS NULL)  AND DispatchLists.iQuantity>0 AND  (CASE WHEN ISNULL(DispatchLists.iNum,0) <>0 THEN (ISNULL(DispatchLists.iQuantity,0)-ISNULL(DispatchLists.fOutQuantity,0)) * (ISNULL(DispatchLists.iNum,0)-ISNULL(DispatchLists.fOutNum,0)) ELSE  (ISNULL(DispatchLists.iQuantity,0)-ISNULL(DispatchLists.fOutQuantity,0)) END) >0
INSERT INTO TempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,fOutQuantity,fOutNum) SELECT  cWhCode, cInvCode,(CASE WHEN cFree1=''THEN NULL ELSE cFree1 END) AS cFree1 , (CASE WHEN cFree2=''THEN NULL ELSE cFree2 END) AS cFree2 , (ISNULL(iQuantity,0)-ISNULL(fOutQuantity,0)) AS fOutQuantity, (ISNULL(iNum,0)-ISNULL(fOutNum,0)) AS fOutNum  FROM SaleBillVouchs INNER JOIN SaleBillVouch ON SaleBillVouch.SBVID=SaleBillVouchs.SBVID  WHERE ISNULL(SaleBillVouchs.cWhCode,'')<>'' AND (SaleBillVouch.iDisp=0 OR SaleBillVouch.iDisp IS NULL) AND ISNULL(cBusType,'')<>'委托' AND ISNULL(cSTCode,'')<>'' AND ISNULL(cInvalider,'')='' AND  SaleBillVouchs.iQuantity>0 AND (CASE WHEN ISNULL(SaleBillVouchs.iNum,0) <>0 THEN (ISNULL(SaleBillVouchs.iQuantity,0)-ISNULL(SaleBillVouchs.fOutQuantity,0)) * (ISNULL(SaleBillVouchs.iNum,0)-ISNULL(SaleBillVouchs.fOutNum,0)) ELSE  (ISNULL(SaleBillVouchs.iQuantity,0)-ISNULL(SaleBillVouchs.fOutQuantity,0)) END) >0
INSERT INTO TempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,fInQuantity,fInNum) SELECT  DispatchLists.cWhCode as cWhCode, DispatchLists.cInvCode as cInvCode,(CASE WHEN DispatchLists.cFree1='' THEN NULL ELSE DispatchLists.cFree1 END) AS cFree1 , (CASE WHEN DispatchLists.cFree2='' THEN NULL ELSE DispatchLists.cFree2 END) AS cFree2 , (ABS(ISNULL(iQuantity,0))-ABS(ISNULL(fOutQuantity,0))) AS fInQuantity, (ABS(ISNULL(iNum,0))-ABS(ISNULL(fOutNum,0))) AS fInNum  FROM DispatchLists INNER JOIN  DispatchList ON DispatchLists.DLID = DispatchList.DLID    WHERE  DispatchList.cVouchType='05' AND ISNULL(DispatchLists.cWhCode,'')<>'' AND ISNULL(DispatchList.cSTCode,'')<>''  AND  (DispatchList.iSale=0 or DispatchList.iSale IS NULL)  AND DispatchLists.iQuantity<0  AND (CASE WHEN ISNULL(DispatchLists.iNum,0) <>0 THEN (ABS(ISNULL(DispatchLists.iQuantity,0))-ABS(ISNULL(DispatchLists.fOutQuantity,0))) * (ABS(ISNULL(DispatchLists.iNum,0))-ABS(ISNULL(DispatchLists.fOutNum,0))) ELSE  (ABS(ISNULL(DispatchLists.iQuantity,0))-ABS(ISNULL(DispatchLists.fOutQuantity,0))) END) >0
INSERT INTO TempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,fInQuantity,fInNum) SELECT  EnDispatchs.cWhCode, EnDispatchs.cInvCode,(CASE WHEN EnDispatchs.cFree1='' THEN NULL ELSE EnDispatchs.cFree1 END) AS cFree1 ,(CASE WHEN EnDispatchs.cFree2='' THEN NULL ELSE EnDispatchs.cFree2 END) AS cFree2 , (ABS(ISNULL(iQuantity,0))-ABS(ISNULL(fOutQuantity,0))) AS fInQuantity, (ABS(ISNULL(iNum,0))-ABS(ISNULL(fOutNum,0))) AS fInNum  FROM EnDispatch INNER JOIN  EnDispatchs ON EnDispatch.EDID = EnDispatchs.EDID WHERE  ISNULL(EnDispatchs.cWhCode,'')<>''  AND ISNULL(EnDispatch.cSTCode,'')<>'' AND EnDispatchs.iQuantity<0 AND bReturnFlag=1 AND (CASE WHEN ISNULL(EnDispatchs.iNum,0) <>0 THEN (ABS(ISNULL(EnDispatchs.iQuantity,0))-ABS(ISNULL(EnDispatchs.fOutQuantity,0))) * (ABS(ISNULL(EnDispatchs.iNum,0))-ABS(ISNULL(EnDispatchs.fOutNum,0))) ELSE  (ABS(ISNULL(EnDispatchs.iQuantity,0))-ABS(ISNULL(EnDispatchs.fOutQuantity,0))) END) >0
INSERT INTO TempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,fInQuantity,fInNum) SELECT  cWhCode, cInvCode,(CASE WHEN cFree1=''THEN NULL ELSE cFree1 END) AS cFree1 , (CASE WHEN cFree2=''THEN NULL ELSE cFree2 END) AS cFree2 , (ABS(ISNULL(iQuantity,0))-ABS(ISNULL(fOutQuantity,0))) AS fInQuantity, ABS((ISNULL(iNum,0))-ABS(ISNULL(fOutNum,0))) AS fInNum  FROM SaleBillVouchs INNER JOIN SaleBillVouch ON SaleBillVouch.SBVID=SaleBillVouchs.SBVID  WHERE ISNULL(SaleBillVouchs.cWhCode,'')<>'' AND (SaleBillVouch.iDisp=0 OR SaleBillVouch.iDisp IS NULL) AND ISNULL(cBusType,'')<>'委托' AND ISNULL(cSTCode,'')<>''  AND ISNULL(cInvalider,'')=''  AND SaleBillVouchs.iQuantity<0 AND (CASE WHEN ISNULL(SaleBillVouchs.iNum,0) <>0 THEN (ABS(ISNULL(SaleBillVouchs.iQuantity,0))-ABS(ISNULL(SaleBillVouchs.fOutQuantity,0))) * (ABS(ISNULL(SaleBillVouchs.iNum,0))-ABS(ISNULL(SaleBillVouchs.fOutNum,0))) ELSE  (ABS(ISNULL(SaleBillVouchs.iQuantity,0))-ABS(ISNULL(SaleBillVouchs.fOutQuantity,0))) END) >0
INSERT INTO CurrentStock(cWhCode,cInvCode,cFree1,cFree2,iQuantity,iNum,fOutQuantity,fOutNum,fInQuantity,fInNum) SELECT  cWhCode, cInvCode,cFree1,cFree2 , sum(round(iQuantity,2)) AS iQuantity, sum(round(ISNULL(iNum,0),2)) AS iNum,sum(ISNULL(fOutQuantity,0)) AS fOutQuantity, sum(ISNULL(fOutNum,0)) AS fOutNum , sum(ISNULL(fInQuantity,0)) AS fInQuantity, sum(ISNULL(fInNum,0)) AS fInNum  FROM TempCurrentStock GROUP BY cWhCode,cInvCode,cFree1,cFree2
UPDATE CurrentStock SET iquantity=0 where Cast(isnull(iquantity,0) as decimal(20,2))=0;UPDATE CurrentStock SET inum=0 where Cast(isnull(inum,0) as decimal(20,2))=0;UPDATE CurrentStock SET foutquantity=0 where Cast(isnull(foutquantity,0) as decimal(20,2))=0;UPDATE CurrentStock SET foutnum=0 where Cast(isnull(foutnum,0) as decimal(20,2))=0;UPDATE CurrentStock SET finquantity=0 where Cast(isnull(finquantity,0) as decimal(20,2))=0;UPDATE CurrentStock SET finnum=0 where Cast(isnull(finnum,0) as decimal(20,2))=0;
IF @@TRANCOUNT > 0 COMMIT TRAN
delete st_totalaccount where isnull(iBeginQuantity,0)=0 and isnull(iBeginNum,0)=0 and isnull(iInComeQuantity,0)=0 and isnull(iInComeNum,0)=0 and isnull(iSentOutQuantity,0)=0 and isnull(iSentOutNum,0)=0 and cinvCode not in (select cinvCode from rdrecords)
delete currentstock where Cast(isnull(iquantity,0) as decimal(20,2))=0 and Cast(isnull(inum,0) as decimal(20,2))=0 and Cast(isnull(foutquantity,0) as Decimal(20,2))=0 and Cast(isnull(foutnum,0) as Decimal(20,2))=0 and Cast(isnull(finquantity,0) as Decimal(20,2))=0 and Cast(isnull(finnum,0) as Decimal(20,2))=0 and cinvCode not in (select cinvCode from rdrecords)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

cqkjll · 发表于 2017-12-4 09:00:48

@startdate取值优化了一下

CREATE  PROCEDURE Pr_CleanStock
AS
Truncate Table CurrentStock
Drop Table TempCurrentStock
declare @startdate varchar(30)
select @startdate=cValue from AccInformation where cSysID='ST' and cName='dSTStartDate'
SELECT cWhCode,cInvCode,(CASE WHEN cFree1=''THEN NULL ELSE cFree1 END) AS cFree1 ,(CASE WHEN cFree2=''THEN NULL ELSE cFree2 END) AS cFree2 ,iBeginQuantity AS iQuantity, iBeginNum as iNum,  CAST(0 AS FLOAT) AS fOutQuantity,CAST(0 AS FLOAT) AS fOutNum,CAST(0 AS FLOAT) AS fInQuantity,CAST(0 AS FLOAT) AS fInNum INTO TempCurrentStock From ST_TotalAccount  Where iMonth=month(@startdate)
INSERT INTO TempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,iQuantity,iNum) SELECT  cWhCode, cInvCode,(CASE WHEN cFree1=''THEN NULL ELSE cFree1 END) AS cFree1 ,(CASE WHEN cFree2=''THEN NULL ELSE cFree2 END) AS cFree2 , (CASE WHEN bRdFlag<>0 THEN  CONVERT(DECIMAL(18,2),iQuantity)  ELSE -CONVERT(DECIMAL(18,2),iQuantity) end) as iQuantity, (CASE WHEN bRdFlag<>0 THEN CONVERT(DECIMAL(18,2),iNum)  ELSE -CONVERT(DECIMAL(18,2),iNum) END) AS iNum FROM RdRecord INNER JOIN RdRecords ON RdRecord.ID = RdRecords.ID WHERE  cVouchType<>'33'AND cVouchType<>'34'  AND RdRecord.dDate>=@startdate
INSERT INTO TempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,fOutQuantity,fOutNum) SELECT  EnDispatchs.cWhCode, EnDispatchs.cInvCode,(CASE WHEN EnDispatchs.cFree1='' THEN NULL ELSE EnDispatchs.cFree1 END) AS cFree1 ,(CASE WHEN EnDispatchs.cFree2='' THEN NULL ELSE EnDispatchs.cFree2 END) AS cFree2 , (ISNULL(iQuantity,0)-ISNULL(fOutQuantity,0)) AS fOutQuantity, (ISNULL(iNum,0)-ISNULL(fOutNum,0)) AS fOutNum  FROM EnDispatch INNER JOIN  EnDispatchs ON EnDispatch.EDID = EnDispatchs.EDID WHERE  ISNULL(EnDispatchs.cWhCode,'')<>''  AND ISNULL(EnDispatch.cSTCode,'')<>'' AND EnDispatchs.iQuantity>0 AND bReturnFlag=0 AND (CASE WHEN ISNULL(EnDispatchs.iNum,0) <>0 THEN (ISNULL(EnDispatchs.iQuantity,0)-ISNULL(EnDispatchs.fOutQuantity,0)) * (ISNULL(EnDispatchs.iNum,0)-ISNULL(EnDispatchs.fOutNum,0)) ELSE  (ISNULL(EnDispatchs.iQuantity,0)-ISNULL(EnDispatchs.fOutQuantity,0)) END) >0
INSERT INTO TempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,fOutQuantity,fOutNum) SELECT  DispatchLists.cWhCode as cWhCode, DispatchLists.cInvCode as cInvCode,(CASE WHEN DispatchLists.cFree1='' THEN NULL ELSE DispatchLists.cFree1 END) AS cFree1 , (CASE WHEN DispatchLists.cFree2='' THEN NULL ELSE DispatchLists.cFree2 END) AS cFree2 , (ISNULL(iQuantity,0)-ISNULL(fOutQuantity,0)) AS fOutQuantity, (ISNULL(iNum,0)-ISNULL(fOutNum,0)) AS fOutNum  FROM DispatchLists INNER JOIN  DispatchList ON DispatchLists.DLID = DispatchList.DLID    WHERE  DispatchList.cVouchType='05' AND ISNULL(DispatchLists.cWhCode,'')<>'' AND ISNULL(DispatchList.cSTCode,'')<>''  AND  (DispatchList.iSale=0 or DispatchList.iSale IS NULL)  AND DispatchLists.iQuantity>0 AND  (CASE WHEN ISNULL(DispatchLists.iNum,0) <>0 THEN (ISNULL(DispatchLists.iQuantity,0)-ISNULL(DispatchLists.fOutQuantity,0)) * (ISNULL(DispatchLists.iNum,0)-ISNULL(DispatchLists.fOutNum,0)) ELSE  (ISNULL(DispatchLists.iQuantity,0)-ISNULL(DispatchLists.fOutQuantity,0)) END) >0
INSERT INTO TempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,fOutQuantity,fOutNum) SELECT  cWhCode, cInvCode,(CASE WHEN cFree1=''THEN NULL ELSE cFree1 END) AS cFree1 , (CASE WHEN cFree2=''THEN NULL ELSE cFree2 END) AS cFree2 , (ISNULL(iQuantity,0)-ISNULL(fOutQuantity,0)) AS fOutQuantity, (ISNULL(iNum,0)-ISNULL(fOutNum,0)) AS fOutNum  FROM SaleBillVouchs INNER JOIN SaleBillVouch ON SaleBillVouch.SBVID=SaleBillVouchs.SBVID  WHERE ISNULL(SaleBillVouchs.cWhCode,'')<>'' AND (SaleBillVouch.iDisp=0 OR SaleBillVouch.iDisp IS NULL) AND ISNULL(cBusType,'')<>'委托' AND ISNULL(cSTCode,'')<>'' AND ISNULL(cInvalider,'')='' AND  SaleBillVouchs.iQuantity>0 AND (CASE WHEN ISNULL(SaleBillVouchs.iNum,0) <>0 THEN (ISNULL(SaleBillVouchs.iQuantity,0)-ISNULL(SaleBillVouchs.fOutQuantity,0)) * (ISNULL(SaleBillVouchs.iNum,0)-ISNULL(SaleBillVouchs.fOutNum,0)) ELSE  (ISNULL(SaleBillVouchs.iQuantity,0)-ISNULL(SaleBillVouchs.fOutQuantity,0)) END) >0
INSERT INTO TempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,fInQuantity,fInNum) SELECT  DispatchLists.cWhCode as cWhCode, DispatchLists.cInvCode as cInvCode,(CASE WHEN DispatchLists.cFree1='' THEN NULL ELSE DispatchLists.cFree1 END) AS cFree1 , (CASE WHEN DispatchLists.cFree2='' THEN NULL ELSE DispatchLists.cFree2 END) AS cFree2 , (ABS(ISNULL(iQuantity,0))-ABS(ISNULL(fOutQuantity,0))) AS fInQuantity, (ABS(ISNULL(iNum,0))-ABS(ISNULL(fOutNum,0))) AS fInNum  FROM DispatchLists INNER JOIN  DispatchList ON DispatchLists.DLID = DispatchList.DLID    WHERE  DispatchList.cVouchType='05' AND ISNULL(DispatchLists.cWhCode,'')<>'' AND ISNULL(DispatchList.cSTCode,'')<>''  AND  (DispatchList.iSale=0 or DispatchList.iSale IS NULL)  AND DispatchLists.iQuantity<0  AND (CASE WHEN ISNULL(DispatchLists.iNum,0) <>0 THEN (ABS(ISNULL(DispatchLists.iQuantity,0))-ABS(ISNULL(DispatchLists.fOutQuantity,0))) * (ABS(ISNULL(DispatchLists.iNum,0))-ABS(ISNULL(DispatchLists.fOutNum,0))) ELSE  (ABS(ISNULL(DispatchLists.iQuantity,0))-ABS(ISNULL(DispatchLists.fOutQuantity,0))) END) >0
INSERT INTO TempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,fInQuantity,fInNum) SELECT  EnDispatchs.cWhCode, EnDispatchs.cInvCode,(CASE WHEN EnDispatchs.cFree1='' THEN NULL ELSE EnDispatchs.cFree1 END) AS cFree1 ,(CASE WHEN EnDispatchs.cFree2='' THEN NULL ELSE EnDispatchs.cFree2 END) AS cFree2 , (ABS(ISNULL(iQuantity,0))-ABS(ISNULL(fOutQuantity,0))) AS fInQuantity, (ABS(ISNULL(iNum,0))-ABS(ISNULL(fOutNum,0))) AS fInNum  FROM EnDispatch INNER JOIN  EnDispatchs ON EnDispatch.EDID = EnDispatchs.EDID WHERE  ISNULL(EnDispatchs.cWhCode,'')<>''  AND ISNULL(EnDispatch.cSTCode,'')<>'' AND EnDispatchs.iQuantity<0 AND bReturnFlag=1 AND (CASE WHEN ISNULL(EnDispatchs.iNum,0) <>0 THEN (ABS(ISNULL(EnDispatchs.iQuantity,0))-ABS(ISNULL(EnDispatchs.fOutQuantity,0))) * (ABS(ISNULL(EnDispatchs.iNum,0))-ABS(ISNULL(EnDispatchs.fOutNum,0))) ELSE  (ABS(ISNULL(EnDispatchs.iQuantity,0))-ABS(ISNULL(EnDispatchs.fOutQuantity,0))) END) >0
INSERT INTO TempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,fInQuantity,fInNum) SELECT  cWhCode, cInvCode,(CASE WHEN cFree1=''THEN NULL ELSE cFree1 END) AS cFree1 , (CASE WHEN cFree2=''THEN NULL ELSE cFree2 END) AS cFree2 , (ABS(ISNULL(iQuantity,0))-ABS(ISNULL(fOutQuantity,0))) AS fInQuantity, ABS((ISNULL(iNum,0))-ABS(ISNULL(fOutNum,0))) AS fInNum  FROM SaleBillVouchs INNER JOIN SaleBillVouch ON SaleBillVouch.SBVID=SaleBillVouchs.SBVID  WHERE ISNULL(SaleBillVouchs.cWhCode,'')<>'' AND (SaleBillVouch.iDisp=0 OR SaleBillVouch.iDisp IS NULL) AND ISNULL(cBusType,'')<>'委托' AND ISNULL(cSTCode,'')<>''  AND ISNULL(cInvalider,'')=''  AND SaleBillVouchs.iQuantity<0 AND (CASE WHEN ISNULL(SaleBillVouchs.iNum,0) <>0 THEN (ABS(ISNULL(SaleBillVouchs.iQuantity,0))-ABS(ISNULL(SaleBillVouchs.fOutQuantity,0))) * (ABS(ISNULL(SaleBillVouchs.iNum,0))-ABS(ISNULL(SaleBillVouchs.fOutNum,0))) ELSE  (ABS(ISNULL(SaleBillVouchs.iQuantity,0))-ABS(ISNULL(SaleBillVouchs.fOutQuantity,0))) END) >0
INSERT INTO CurrentStock(cWhCode,cInvCode,cFree1,cFree2,iQuantity,iNum,fOutQuantity,fOutNum,fInQuantity,fInNum) SELECT  cWhCode, cInvCode,cFree1,cFree2 , sum(round(iQuantity,2)) AS iQuantity, sum(round(ISNULL(iNum,0),2)) AS iNum,sum(ISNULL(fOutQuantity,0)) AS fOutQuantity, sum(ISNULL(fOutNum,0)) AS fOutNum , sum(ISNULL(fInQuantity,0)) AS fInQuantity, sum(ISNULL(fInNum,0)) AS fInNum  FROM TempCurrentStock GROUP BY cWhCode,cInvCode,cFree1,cFree2
UPDATE CurrentStock SET iquantity=0 where Cast(isnull(iquantity,0) as decimal(20,2))=0;UPDATE CurrentStock SET inum=0 where Cast(isnull(inum,0) as decimal(20,2))=0;UPDATE CurrentStock SET foutquantity=0 where Cast(isnull(foutquantity,0) as decimal(20,2))=0;UPDATE CurrentStock SET foutnum=0 where Cast(isnull(foutnum,0) as decimal(20,2))=0;UPDATE CurrentStock SET finquantity=0 where Cast(isnull(finquantity,0) as decimal(20,2))=0;UPDATE CurrentStock SET finnum=0 where Cast(isnull(finnum,0) as decimal(20,2))=0;
IF @@TRANCOUNT > 0 COMMIT TRAN
delete st_totalaccount where isnull(iBeginQuantity,0)=0 and isnull(iBeginNum,0)=0 and isnull(iInComeQuantity,0)=0 and isnull(iInComeNum,0)=0 and isnull(iSentOutQuantity,0)=0 and isnull(iSentOutNum,0)=0 and cinvCode not in (select cinvCode from rdrecords)
delete currentstock where Cast(isnull(iquantity,0) as decimal(20,2))=0 and Cast(isnull(inum,0) as decimal(20,2))=0 and Cast(isnull(foutquantity,0) as Decimal(20,2))=0 and Cast(isnull(foutnum,0) as Decimal(20,2))=0 and Cast(isnull(finquantity,0) as Decimal(20,2))=0 and Cast(isnull(finnum,0) as Decimal(20,2))=0 and cinvCode not in (select cinvCode from rdrecords)

comer · 发表于 2017-12-4 22:43:47

这么长啊，不会吧

nazi8888 · 发表于 2017-12-10 17:09:57

这个复制，并执行就可以了么？？

cqkjll · 发表于 2017-12-13 00:51:38

nazi8888 发表于 2017-12-10 17:09
这个复制，并执行就可以了么？？

把它打到账套数据库中执行后创建存储过程，然后直接执行这个存储过程就可以了啊

HughZ · 发表于 2017-12-28 15:50:43

感谢您的分享

nazi8888 · 发表于 2019-5-1 13:13:30

谢谢分享！

oupeizhi · 发表于 2019-9-19 17:35:34

谢谢，学习了！

账号		自动登录	找回密码
密码			注册账号

初学者课程：T3自学\|T6自学\|U8自学	软件下载｜课件下载｜工具下载	资料：通资料\|U8资料\|NC\|培训\|年结	积分规则 \| 使用常见问题Q&A
知识库:U8 \| 通 \| NC \| U9 \| OA \| 政务	U8\|U9\|NCC\|NC65\|NC65客开\|NCC客开	新手必读 \| 任务 \| 快速增金币	用友QQ群[微信群]

[经验] T3V10_V11整理现存量存储过程分享

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

相关帖子