进销存报表已经完成！我做成的没有参数，不能灵活的控制时间！
请大家给加个！
查询的条件是哪个月为本月
红线的StateDate为添加参数地方！目前那个是固定死的！
下面如果感觉不太符合常规！你可以加以改正！如果可以控制以为那个月份为本月即可！
- SQL code
/****** Object: StoredProcedure [dbo].[P_Wms_StockMoneyState] Script Date: 07/23/2012 14:50:46 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER proc [dbo].[P_Wms_StockMoneyState] as --declare @month_last varchar(20) --上月 --declare @month_this varchar(20) --本月 -- set @month_last = DATEADD(Month,-1,DATEADD(s,-1,CONVERT(char(8),DATEADD(Month,1,getdate()),120)+'1')) --set @month_this = Convert(nvarchar(20),Cast(getdate() AS DateTime),23) 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, --本月入库合计金额 ThisSaleQty int not null default 0, --本月销售数量 ThisSaleMoney numeric(12,2) not null default 0, --本月销售金额 ThisPoRtnOutQty int not null default 0, --本月退产数量 ThisPoRtnOutMoney numeric(12,2) not null default 0, --本月退产金额 ThisAdjustOutQty int not null default 0, --本月盘亏数量 ThisAdjustOutMoney numeric(12,2) not null default 0, --本月盘亏金额 ThisIsGiftOutQty int not null default 0, --本月赠送出库数量 ThisIsGiftOutMoney numeric(12,2) not null default 0, --本月赠送出库金额 ThisTotalOutQty int not null default 0, --本月出库合计数量 ThisTotalOutMoney numeric(12,2) not null default 0, --本月出库合计金额 ThisMonthTotalQty int not null default 0, --本月月结存数量 ThisMonthTotalMoney numeric(12,2) not null default 0, --本月月结存金额 ) --上月的数据汇总到临时表#t1 select t2.SkuNo,t2.ProductName ,isnull(Sum(t1.BeginCostPrice*t1.BeginQty)/nullif(sum(t1.BeginQty),0),0)as LastMonthAvgPrice--上月平均单价 ,Sum(t1.BeginQty)as LastMonthCurrQty --上月初数量, ,Sum(t1.BeginCostPrice*t1.BeginQty)as LastMonthSumPrice --上月初金额 into #t1 from Wms_StockDailyState as t1 left join V_Prod_Sku t2 on t1.SkuId=t2.SkuId where [color=#FF0000] StateDate<= DATEADD(Month,-1,DATEADD(s,-1,CONVERT(char(8),DATEADD(Month,1,getdate()),120)+'1'))[/color] group by t2.SkuNo,t2.ProductName--StateDate<= DATEADD(Month,-1,DATEADD(s,-1,CONVERT(char(8),DATEADD(Month,1,getdate--这个就是上月份的测试select DATEADD(Month,-1,DATEADD(s,-1,CONVERT(char(8),DATEADD(Month,1,getdate()),120)+'1'))--2012-06-30 23:59:59.000 --本月的数据汇总到临时表#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 --本月入库合计数量 ,sum(t1.SaleOutQty) as ThisSaleQty --本月销售数量 ,sum(t1.PoRtnOutQty) as ThisPoRtnOutQty --本月退产数量 ,sum(t1.AdjustOutQty) as ThisAdjustOutQty --本月盘亏数量 ,sum(t1.IsGiftOutQty)as ThisIsGiftOutQty --本月赠品数量 ,sum(t1.SaleOutQty+t1.PoRtnOutQty+t1.AdjustOutQty+t1.IsGiftOutQty)as ThisTotalOutQty --本月出库合计数量 into #t2 from Wms_StockDailyState as t1 left join V_Prod_Sku t2 on t1.SkuId=t2.SkuId[b] where StateDate>DATEADD(Month,-1,DATEADD(s,-1,CONVERT(char(8),DATEADD(Month,1,getdate()),120)+'1'))and StateDate<'2012-7-31 23:59:59'[/b]这个是本月的--StateDate<= DATEADD(Month,-1,DATEADD(s,-1,CONVERT(char(8),DATEADD(Month,1,getdate--这个就是上月份的测试select DATEADD(Month,-1,DATEADD(s,-1,CONVERT(char(8),DATEADD(Month,1,getdate()),120)+'1'))--2012-07-30 23:59:59.000 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), --本月入库合计数量 isnull(#t2.ThisSaleQty,0), isnull(#t2.ThisPoRtnOutQty,0), isnull(#t2.ThisAdjustOutQty,0) , isnull(#t2.ThisIsGiftOutQty,0), isnull(#t2.ThisTotalOutQty,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),--本月入库合计总金额ThisSaleMoney = isnull(case ThisSaleQty when 0 then 0 else Round((LastMonthSumPrice + ThisInSumPrice)/nullif(LastMonthCurrQty + ThisMonthInQty,0)*(ThisSaleQty),2) end,0),--本月销售金额ThisPoRtnOutMoney = isnull(case ThisPoRtnOutQty when 0 then 0 else Round((LastMonthSumPrice + ThisInSumPrice)/nullif(LastMonthCurrQty + ThisMonthInQty,0)*(ThisPoRtnOutQty),2) end,0),--本月退产金额ThisAdjustOutMoney = isnull(case ThisAdjustOutQty when 0 then 0 else Round((LastMonthSumPrice + ThisInSumPrice)/nullif(LastMonthCurrQty + ThisMonthInQty,0)*(ThisAdjustOutQty),2) end,0),--本月盘亏金额ThisIsGiftOutMoney = isnull(case ThisIsGiftOutQty when 0 then 0 else Round((LastMonthSumPrice + ThisInSumPrice)/nullif(LastMonthCurrQty + ThisMonthInQty,0)*(ThisIsGiftOutQty),2) end,0),--本月赠送出库金额ThisTotalOutMoney = isnull(case ThisTotalOutQty when 0 then 0 else Round((LastMonthSumPrice + ThisInSumPrice)/nullif(LastMonthCurrQty + ThisMonthInQty,0)*(ThisTotalOutQty),2) end,0),--本月出库合计总金额--本月月结存数量ThisMonthTotalQty = isnull(case(LastMonthCurrQty+ThisTotalInQty-ThisTotalOutQty) when 0 then 0 else (LastMonthCurrQty+ThisTotalInQty-ThisTotalOutQty) end,0),--本月月结存金额ThisMonthTotalMoney = case (LastMonthCurrQty+ThisTotalInQty-ThisTotalOutQty) when 0 then 0 else Round((LastMonthSumPrice + ThisInSumPrice)/nullif(LastMonthCurrQty + ThisMonthInQty,0)*((LastMonthCurrQty+ThisTotalInQty-ThisTotalOutQty)),2) end select * from #rep GO