27,579
社区成员
发帖
与我相关
我的任务
分享
/****** Object: StoredProcedure [dbo].[P_Wms_StockMoneyState] Script Date: 07/23/2012 14:50:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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 StateDate<= DATEADD(Month,-1,DATEADD(s,-1,CONVERT(char(8),DATEADD(Month,1,getdate()),120)+'1')) 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
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'
这个是本月的
--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
ALTER proc [dbo].[P_Wms_StockMoneyState](
@t datetime //传入时间
)
as
begin
--利用@t 做查询
...
end
declare @datetime varchar(30)
set @datetime ='2012-04-21 12:33:22'
SELECT @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
select cast(@datetime as datetime),dateadd(month,1,@datetime)
set @datetime ='2012-04-21'
SELECT @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
select cast(@datetime as datetime),dateadd(month,1,@datetime)
set @datetime ='2012-04'
SELECT @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
select cast(@datetime as datetime),dateadd(month,1,@datetime)
set @datetime ='2012-4'
SELECT @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
select cast(@datetime as datetime),dateadd(month,1,@datetime)
--输出:
2012-04-01 00:00:00.000 2012-05-01 00:00:00.000
2012-04-01 00:00:00.000 2012-05-01 00:00:00.000
2012-04-01 00:00:00.000 2012-05-01 00:00:00.000
2012-04-01 00:00:00.000 2012-05-01 00:00:00.000
参数是否合法,只需要在最前面进行相关的处理转化就可以了。
declare @datetime varchar(30)
set @datetime ='2012-04-21 12:33:22'
SELECT @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
select cast(@datetime as datetime),dateadd(month,1,@datetime)
set @datetime ='2012-04-21'
SELECT @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
select cast(@datetime as datetime),dateadd(month,1,@datetime)
set @datetime ='2012-04'
SELECT @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
select cast(@datetime as datetime),dateadd(month,1,@datetime)
set @datetime ='2012-4'
SELECT @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
select cast(@datetime as datetime),dateadd(month,1,@datetime)
ALTER proc [dbo].[P_Wms_StockMoneyState]
--0001 在这里添加参数
(
@datetime varchar(30)=''--格式可以为'2012-07-24 12:22:34' or '2012-07-24'
)
as
--0001 修改时间
declare @cur_datetime datetime
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(@datetime as datetime),120)+'1') end
--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
---StateDate<= DATEADD(Month,-1,DATEADD(s,-1,CONVERT(char(8),DATEADD(Month,1,getdate()),120)+'1'))
---0001修改为
StateDate<@cur_datetime
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
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'
---0001修改为
StateDate>=@cur_datetime and StateDate<dateadd(month,1,@cur_datetime )
---这个是本月的
--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
ALTER proc [dbo].[P_Wms_StockMoneyState]
(
@Date VARCHAR(10) = ''
)
as
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 CONVERT(VARCHAR(6),StateDate,120) = CONVERT(VARCHAR(7),DATEADD(MONTH,-1,@Date),120) --上月
where StateDate<convert(datetime,convert(varchar(7),dateadd(month,0,@Date),120)+'-01')
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 --本月入库合计数量
,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
--where CONVERT(VARCHAR(6),StateDate,120) = CONVERT(VARCHAR(6),@Date,120) --本月
where StateDate>=convert(datetime,convert(varchar(7),dateadd(month,0,@Date),120)+'-01') and
StateDate<DATEADD(Month,0,DATEADD(s,-1,CONVERT(char(8),DATEADD(Month,1,@Date),120)+'1'))
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
--上月份
where StateDate<convert(datetime,convert(varchar(7),dateadd(month,0,@t),120)+'-01')
--本月分
where StateDate>=convert(datetime,convert(varchar(7),dateadd(month,0,@Date),120)+'-01') and
StateDate<DATEADD(Month,0,DATEADD(s,-1,CONVERT(char(8),DATEADD(Month,1,@Date),120)+'1'))
declare @month_last varchar(20) --上月最一天
declare @month_this varchar(20) --本月最后一天
declare @dt varchar(20)
set @dt='2012-05-06'
set @month_last= convert(varchar(10),dateadd(day,-1,(CONVERT(char(8),@dt,120)+'1')),120)+' 23:59:59' --上月第一天
set @month_this=convert(varchar(10),DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+'1'),120)+' 00:00:00'--当月最后一天
select @month_last
select @month_this
--------------------
2012-04-30 23:59:59
(1 行受影响)
--------------------
2012-05-31 00:00:00
(1 行受影响)