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.SkuId
where 

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.ThisTotalInQt