日期:2014-05-18 浏览次数:20602 次
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER proc [dbo].[Pd_TB_Profit_CP] (@FDateB datetime,@ssql varchar(8000)) as set nocount on SET ANSI_WARNINGS OFF select t1.FItemName,sum(t1.FQty) FQty,sum(t1.FDAmount) FDAmount,sum(isnull(t1.Fcost,0)) Fcost,sum(isnull(t2.FDDHLFee,0)*(isnull(t1.FDWeight/t2.FWeight,0))) FDDHLFee, sum(isnull(t2.FDHLFee*(t1.FDWeight/t2.FWeight),0)) FDHLFee, sum(isnull(t1.FDAmount,0)+(isnull(t2.FDDHLFee,0)*(t1.FDWeight/t2.FWeight))-(isnull(t1.Fcost,0)+(isnull(t2.FDHLFee,0)*(t1.FDWeight/t2.FWeight)))) FZprofit, sum(isnull(t1.FDAmount,0)+(isnull(t2.FDDHLFee,0)*(t1.FDWeight/t2.FWeight))-(isnull(t1.Fcost,0)+(isnull(t2.FDHLFee,0)*(t1.FDWeight/t2.FWeight))))/count(distinct t1.fid) FDprofit from SU_Sale_TBStockBillEntry t1 inner join SU_Sale_TBStockBill t2 on t1.fid=t2.fid where CONVERT(varchar(6),[FDate],112)=@FdateB GROUP BY t1.FItemName union all select '合计', sum(t1.FQty),sum(t1.FDAmount),sum(t1.Fcost),sum(isnull(t2.FDDHLFee,0)*(isnull(t1.FDWeight/t2.FWeight,0))), sum(isnull(t2.FDHLFee*(t1.FDWeight/t2.FWeight),0)), sum(isnull(t1.FDAmount,0)+(isnull(t2.FDDHLFee,0)*(t1.FDWeight/t2.FWeight))-(isnull(t1.Fcost,0)+(isnull(t2.FDHLFee,0)*(t1.FDWeight/t2.FWeight)))), sum(isnull(t1.FDAmount,0)+(isnull(t2.FDDHLFee,0)*(t1.FDWeight/t2.FWeight))-(isnull(t1.Fcost,0)+(isnull(t2.FDHLFee,0)*(t1.FDWeight/t2.FWeight))))/count(distinct t1.fid) from SU_Sale_TBStockBillEntry t1 inner join SU_Sale_TBStockBill t2 on t1.fid=t2.fid where CONVERT(varchar(6),[FDate],112)=@FdateB