set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[STPGridList_Sood] @__StartDate char(8)='00/00/00', @__EndDate char(8)='99/99/99', @FormCaption nvarchar(1000) output AS if not (@__StartDate='00/00/00' and @__EndDate='99/99/99') set @FormCaption='<' + @__EndDate + '~' + @__StartDate +'>' declare @FirstRemainKala money,@SndRemainKala money,@AllBuy money,@AllSell Money,@AllCost money,@Sood money SELECT @FirstRemainKala =SUM(FirstRemainAmount) ,@SndRemainKala= SUM(EndRemainAmount) FROM (SELECT StockTbl.GoodSyscode, CAST(CASE WHEN AvgStart.avgfee IS NULL THEN 0 ELSE AvgStart.avgfee * remain_start.remain END AS money) AS FirstRemainAmount, CAST(CASE WHEN Avg_End.avgfee IS NULL THEN 0 ELSE Avg_End.avgfee * remain_enddate.remain END AS money) AS EndRemainAmount FROM dbo.FunGoodRemainByDate(@__StartDate) AS Remain_Start RIGHT OUTER JOIN StockTbl ON Remain_Start.GoodSysCode = StockTbl.GoodSyscode LEFT OUTER JOIN dbo.FunGoodAvgFeeByDate(@__StartDate) AS AVGStart ON StockTbl.GoodSyscode = AVGStart.GoodSysCode LEFT OUTER JOIN dbo.FunGoodAvgFeeByDate(@__EndDate) AS AVG_End ON StockTbl.GoodSyscode = AVG_End.GoodSysCode LEFT OUTER JOIN dbo.FunGoodRemainByDate(@__EndDate) AS Remain_EndDate ON StockTbl.GoodSyscode = Remain_EndDate.GoodSysCode) AS derivedtbl_1 SELECT @AllBuy= SUM(VWDocumentDetail_DocHeader.AmmountIN - VWDocumentDetail_DocHeader.AmmountOUT) FROM CodeMNGDWNTbl LEFT OUTER JOIN VWDocumentDetail_DocHeader ON CodeMNGDWNTbl.AccountSysCode = VWDocumentDetail_DocHeader.Fk_AccountSyscode WHERE (VWDocumentDetail_DocHeader.DocDate BETWEEN @__StartDate AND @__Enddate) AND (CodeMNGDWNTbl.FK_AccountSysCode0 = 2 OR CodeMNGDWNTbl.FK_AccountSysCode0 = 16) SELECT @AllSell= -SUM(VWDocumentDetail_DocHeader.AmmountIN - VWDocumentDetail_DocHeader.AmmountOUT) FROM CodeMNGDWNTbl LEFT OUTER JOIN VWDocumentDetail_DocHeader ON CodeMNGDWNTbl.AccountSysCode = VWDocumentDetail_DocHeader.Fk_AccountSyscode WHERE (VWDocumentDetail_DocHeader.DocDate BETWEEN @__StartDate AND @__Enddate) AND (CodeMNGDWNTbl.FK_AccountSysCode0 = 14 OR CodeMNGDWNTbl.FK_AccountSysCode0 = 15) SELECT @allCost= SUM(VWDocumentDetail_DocHeader.AmmountIN - VWDocumentDetail_DocHeader.AmmountOUT) FROM CodeMNGDWNTbl LEFT OUTER JOIN VWDocumentDetail_DocHeader ON CodeMNGDWNTbl.AccountSysCode = VWDocumentDetail_DocHeader.Fk_AccountSyscode WHERE (VWDocumentDetail_DocHeader.DocDate BETWEEN @__StartDate AND @__Enddate) AND (CodeMNGDWNTbl.FK_AccountSysCode0 = 3) declare @a table (FirstRemain money ,SeccondRemain money ,AllBuy money ,AllSell money ,AllCost money , Sood money ) if @FirstRemainKala is null set @FirstRemainKala=0 if @SndRemainKala is null set @SndRemainKala=0 if @AllBuy is null set @AllBuy=0 if @AllSell is null set @AllSell=0 if @allCost is null set @allCost=0 set @sood = -@FirstRemainKala-@AllBuy+@SndRemainKala+@AllSell-@allCost INSERT INTO @a (FirstRemain ,SeccondRemain ,AllBuy ,AllSell ,AllCost , Sood ) VALUES ( @FirstRemainKala ,@SndRemainKala,@AllBuy,@AllSell,@allCost,@sood) select * from @a