把存储过程参数修改成2个参数的！时间和结束时间
需求：可以查询 本月的几天--本月几天的数据
可以查询 比如：2012-5-1 -----2012-7-26日以7月为本月
可以查询 比如：2012-4-20---------2012-6-15日6月为本月
可以查询 比如：2012-7-20---------2012-7-21日 以7月为本月
当不输入开始日期和结束日期时候 直接查询 以系统目前月份为本月
ALTER proc [dbo].[P_Wms_StockMoneyState] (@datetime varchar(30)='') as declare @cur_datetime varchar (30) select @cur_datetime=case when @datetime = '' or @datetime is null then CONVERT(datetime,CONVERT(char(8),getdate(),120)+'1') else CONVERT(datetime,CONVERT(char(8),cast(case when len(@datetime)=7 or len(@datetime)=6 then @datetime+'-01' else @datetime end as datetime),120)+'1') end create table #rep (Id int identity(1,1), --序列号 SkuNo varchar(60), --商品编号 ProductName varchar(100), --商品名称 LastMonthAvgPrice numeric(12,2) not null default 0, --上月平均单价 LastMonthCurrQty int not null default 0, --上月初数量 LastMonthSumPrice numeric(12,2) not null default 0, --上月初金额 ThisMonthInQty int not null default 0, --本月进货数量 ThisMonthInPrice numeric(12,2) not null default 0, --本月进货单价 ThisInSumPrice numeric(12,2) not null default 0, --本月进货金额 ThisMonthAvgPrice numeric(12,2) not null default 0, --本月平均单价 ThisSaleRtnQty int not null default 0, --本月销售退回数量 ThisSaleRtnMoney numeric(12,2) not null default 0, --本月销售退回金额 ThisAdjustInQty int not null default 0, --本月盘盈数量 ThisAdjustInMoney numeric(12,2) not null default 0, --本月盘盈金额 ThisTotalInQty int not null default 0, --本月入库合计数量 ThisTotalInMoney numeric(12,2) not null default 0, --本月入库合计金额 ) --上月的数据汇总到临时表#t1 select t2.SkuNo,t2.ProductName --上月平均单价 ,isnull(case when sum(t1.BeginQty)>0 then 0 else Sum(t1.BeginCostPrice*t1.BeginQty)/nullif(sum(t1.BeginQty),0)end,0)as LastMonthAvgPrice ,isnull(Sum(t1.BeginQty),0)as LastMonthCurrQty --上月初数量, ,isnull(Sum(t1.BeginCostPrice*t1.BeginQty),0)as LastMonthSumPrice --上月初金额 into #t1 from Wms_StockDailyState as t1 left join V_Prod_Sku t2 on t1.SkuId=t2.SkuId where StateDate<@cur_datetime group by t2.SkuNo,t2.ProductName --本月的数据汇总到临时表#t2 select t2.SkuNo,t2.ProductName ,Sum(t1.InQty)as ThisMonthInQty --本月进货数量 ,Sum(t1.InPrice)as ThisMonthInPrice --本月进货单价 ,Sum(t1.InTaxAmt)as ThisInSumPrice --本月进货金额 ,SUM(t1.SaleRtnInQty)as ThisSaleRtnQty --本月销售退回数量 ,sum(t1.AdjustInQty)as ThisAdjustInQty --本月盘盈数量 ,sum(t1.InQty+t1.SaleOutQty+t1.AdjustInQty)as ThisTotalInQty --本月入库合计数量 into #t2 from Wms_StockDailyState as t1 left join V_Prod_Sku t2 on t1.SkuId=t2.SkuIdwhere StateDate>=@cur_datetime and StateDate<dateadd(month,1,@cur_datetime ) group by t2.SkuNo,t2.ProductName insert into #rep (SkuNo,ProductName,LastMonthAvgPrice,LastMonthCurrQty,LastMonthSumPrice,ThisMonthInQty,ThisMonthInPrice,ThisInSumPrice, ThisSaleRtnQty,ThisAdjustInQty,ThisTotalInQty,ThisSaleQty,ThisPoRtnOutQty,ThisAdjustOutQty,ThisIsGiftOutQty,ThisTotalOutQty ) select isnull(#t1.SkuNo,#t2.SkuNo), isnull(#t1.ProductName,#t2.ProductName), isnull(#t1.LastMonthAvgPrice,0), isnull(#t1.LastMonthCurrQty,0), isnull(#t1.LastMonthSumPrice,0), isnull(#t2.ThisMonthInQty,0), isnull(#t2.ThisMonthInPrice,0), isnull(#t2.ThisInSumPrice,0), isnull(#t2.ThisSaleRtnQty,0), isnull(#t2.ThisAdjustInQty,0), isnull(#t2.ThisTotalInQty,0), --本月入库合计数量 from #t1 full join #t2 on #t1.SkuNo = #t2.SkuNo update #rep set ThisMonthAvgPrice = isnull(case LastMonthCurrQty + ThisMonthInQty when 0 then 0 else Round((LastMonthSumPrice + ThisInSumPrice)/nullif(LastMonthCurrQty + ThisMonthInQty,0),2)end,0),--本月平均价格ThisSaleRtnMoney = isnull(case ThisSaleRtnQty when 0 then 0 else Round((LastMonthSumPrice + ThisInSumPrice)/nullif(LastMonthCurrQty + ThisMonthInQty,0)*(ThisSaleRtnQty),2) end,0),--本月销售退回金额ThisAdjustInMoney = isnull(case ThisAdjustInQty when 0 then 0 else Round((LastMonthSumPrice + ThisInSumPrice)/nullif(LastMonthCurrQty + ThisMonthInQty,0)*(ThisAdjustInQty),2) end,0),--本月盘盈金额ThisTotalInMoney = isnull(case ThisTotalInQty when 0 then 0 else Round((LastMonthSumPrice + ThisInSumPrice)/nullif(LastMonthCurrQty + ThisMonthInQty,0)*(ThisTotalInQty),2) end,0),--本月入库合计总金额 select * from #rep
ALTER proc [dbo].[P_Wms_StockMoneyState] ( @from varchar(30)='',--'2012-05-01' @to varchar(30)=''--'2012-07-26')as begin --declare @from varchar(30) --declare @to varchar(30) --set @from ='2012-05-01' --set @to='' if @from >@to begin print 'error' return end declare @from_datetime datetime declare @to_datetime datetime select @from_datetime=case when @from = '' or @from is null then CONVERT(datetime,CONVERT(char(8),getdate(),120)+'1') else CONVERT(datetime,CONVERT(char(8),cast(case when len(@from)=7 or len(@from)=6 then @from+'-01' else @from end as datetime),120)+'1') end select @to_datetime=case when @to = '' or @to is null then CONVERT(datetime,CONVERT(char(8),getdate(),120)+'1') else CONVERT(datetime,CONVERT(char(8),cast(case when len(@to)=7 or len(@to)=6 then @to+'-01' else @to end as datetime),120)+'1') end select @from_datetime,@to_datetime create table #rep (Id int identity(1,1), --序列号 SkuNo varchar(60), --商品编号 ProductName varchar(100), --商品名称 LastMonthAvgPrice numeric(12,2) not null default 0, --上月平均单价 LastMonthCurrQty int not null default 0, --上月初数量 LastMonthSumPrice numeric(12,2) not null default 0, --上月初金额 ThisMonthInQty int not null default 0, --本月进货数量 ThisMonthInPrice numeric(12,2) not null default 0, --本月进货单价 ThisInSumPrice numeric(12,2) not null default 0, --本月进货金额 ThisMonthAvgPrice numeric(12,2) not null default 0, --本月平均单价 ThisSaleRtnQty int not null default 0, --本月销售退回数量 ThisSaleRtnMoney numeric(12,2) not null default 0, --本月销售退回金额 ThisAdjustInQty int not null default 0, --本月盘盈数量 ThisAdjustInMoney numeric(12,2) not null default 0, --本月盘盈金额 ThisTotalInQty int not null default 0, --本月入库合计数量 ThisTotalInMoney numeric(12,2) not null default 0, --本月入库合计金额 ) --上月的数据汇总到临时表#t1 if @from <@to begin select t2.SkuNo,t2.ProductName --上月平均单价 ,isnull(case when sum(t1.BeginQty)>0 then 0 else Sum(t1.BeginCostPrice*t1.BeginQty)/nullif(sum(t1.BeginQty),0)end,0)as LastMonthAvgPrice ,isnull(Sum(t1.BeginQty),0)as LastMonthCurrQty --上月初数量, ,isnull(Sum(t1.BeginCostPrice*t1.BeginQty),0)as LastMonthSumPrice --上月初金额 into #t1 from Wms_StockDailyState as t1 left join V_Prod_Sku t2 on t1.SkuId=t2.SkuId ---where StateDate<@cur_datetime --0001 where StateDate>=@from_datetime and StateDate<@to_datetime group by t2.SkuNo,t2.ProductName end else begin select t2.SkuNo,t2.ProductName --上月平均单价 ,isnull(case when sum(t1.BeginQty)>0 then 0 else Sum(t1.BeginCostPrice*t1.BeginQty)/nullif(sum(t1.BeginQty),0)end,0)as LastMonthAvgPrice ,isnull(Sum(t1.BeginQty),0)as LastMonthCurrQty --上月初数量, ,isnull(Sum(t1.BeginCostPrice*t1.BeginQty),0)as LastMonthSumPrice --上月初金额 into #t1 from Wms_StockDailyState as t1 left join V_Prod_Sku t2 on t1.SkuId=t2.SkuId where StateDate<@to_datetime end --本月的数据汇总到临时表#t2 select t2.SkuNo,t2.ProductName ,Sum(t1.InQty)as ThisMonthInQty --本月进货数量 ,Sum(t1.InPrice)as ThisMonthInPrice --本月进货单价 ,Sum(t1.InTaxAmt)as ThisInSumPrice --本月进货金额 ,SUM(t1.SaleRtnInQty)as ThisSaleRtnQty --本月销售退回数量 ,sum(t1.AdjustInQty)as ThisAdjustInQty --本月盘盈数量 ,sum(t1.InQty+t1.SaleOutQty+t1.AdjustInQty)as ThisTotalInQty --本月入库合计数量 into #t2 from Wms_StockDailyState as t1 left join V_Prod_Sku t2 on t1.SkuId=t2.SkuId --where StateDate>=@cur_datetime and StateDate<dateadd(month,1,@cur_datetime ) --0001 where StateDate>=@to_datetime and StateDate<dateadd(month,1,@to_datetime ) group by t2.SkuNo,t2.ProductName insert into #rep (SkuNo,ProductName,LastMonthAvgPrice,LastMonthCurrQty,LastMonthSumPrice,ThisMonthInQty,ThisMonthInPrice,ThisInSumPrice, ThisSaleRtnQty,ThisAdjustInQty,ThisTotalInQty,ThisSaleQty,ThisPoRtnOutQty,ThisAdjustOutQty,ThisIsGiftOutQty,ThisTotalOutQty) select isnull(#t1.SkuNo,#t2.SkuNo), isnull(#t1.ProductName,#t2.ProductName), isnull(#t1.LastMonthAvgPrice,0), isnull(#t1.LastMonthCurrQty,0), isnull(#t1.LastMonthSumPrice,0), isnull(#t2.ThisMonthInQty,0), isnull(#t2.ThisMonthInPrice,0), isnull(#t2.ThisInSumPrice,0), isnull(#t2.ThisSaleRtnQty,0), isnull(#t2.ThisAdjustInQty,0), isnull(#t2.ThisTotalInQty,0), --本月入库合计数量 from #t1 full join #t2 on #t1.SkuNo = #t2.SkuNo update #rep set ThisMonthAvgPrice = isnull(case LastMonthCurrQty + ThisMonthInQty when 0 then 0 else Round((LastMonthSumPrice + ThisInSumPrice)/nullif(LastMonthCurrQty + ThisMonthInQty,0),2)end,0),--本月平均价格 ThisSaleRtnMoney = isnull(case ThisSaleRtnQty when 0 then 0 else Round((LastMonthSumPrice + ThisInSumPrice)/nullif(LastMonthCurrQty + ThisMonthInQty,0)*(ThisSaleRtnQty),2) end,0),--本月销售退回金额 ThisAdjustInMoney = isnull(case ThisAdjustInQty when 0 then 0 else Round((LastMonthSumPrice + ThisInSumPrice)/nullif(LastMonthCurrQty + ThisMonthInQty,0)*(ThisAdjustInQty),2) end,0),--本月盘盈金额 ThisTotalInMoney = isnull(case ThisTotalInQty when 0 then 0 else Round((LastMonthSumPrice + ThisInSumPrice)/nullif(LastMonthCurrQty + ThisMonthInQty,0)*(ThisTotalInQty),2) end,0),--本月入库合计总金额 select * from #repend
