[CODE=SQL]
加权平均参考
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[td_purchase_s]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[td_purchase_s]
GO
CREATE procedure tdsp_purcost
(@purprice numeric (8,2),@normaldis numeric(4,2),@specdis numeric(4,2),@commision numeric(7,2),
@promosum numeric(7,2),@promoway bit,@arc tinyint,@purcost numeric(10,4) output)
with recompile,encryption
as
select @purprice=isnull(@purprice,0)
select @normaldis=isnull(@normaldis,0)
select @specdis=isnull(@specdis,0)
select @commision=isnull(@commision,0)
select @promosum=isnull(@promosum,0)
select @promoway=isnull(@promoway,1)
select @arc=isnull(@arc,1)
if @arc>4
begin
raiserror("错误算法标志",16,1)
return
end
select @promoway=isnull(@promoway,1)
if @promoway=0
begin
if @arc=1
begin
select @purcost=(@purprice*(1-@normaldis/100-@specdis/100)-@commision)-@promosum
end
if @arc=2
begin
select @purcost=(@purprice*(1-@normaldis/100)*(1-@specdis/100)-@commision)-@promosum
end
if @arc=3
begin
select @purcost=(@purprice-@commision)*(1-@normaldis/100-@specdis/100)-@promosum
end
if @arc=4
begin
select @purcost=(@purprice-@commision)*(1-@normaldis/100)*(1-@specdis/100)-@promosum
end
return
end
else
begin
if @arc=1
begin
select @purcost=(@purprice*(1-@normaldis/100-@specdis/100)-@commision)*(1-@promosum/100)
end
if @arc=2
begin
select @purcost=(@purprice*(1-@normaldis/100)*(1-@specdis/100)-@commision)*(1-@promosum/100)
end
if @arc=3
begin
select @purcost=(@purprice-@commision)*(1-@normaldis/100-@specdis/100)*(1-@promosum/100)
end
if @arc=4
begin
select @purcost=(@purprice-@commision)*(1-@normaldis/100)*(1-@specdis/100)*(1-@promosum/100)
end
return
end
--创建入库信息
declare @tbin table(时间 datetime,数量 int,单价 int,商品ID int)
insert into @tbin
select '2003.1.1',10,10,1
union all select '2003.1.5',20,15,1
union all select '2003.1.8',15,20,1
union all select '2003.1.5',20,15,2
union all select '2003.1.8',15,20,2
--创建出库信息
declare @tbout table(时间 datetime,数量 int,商品ID int)
insert into @tbout
select '2003.1.2',5,1
union all select '2003.1.8',260,1
union all select '2003.1.8',26,2
--创建入库临时表
declare @in table(id int identity(1,1),时间 datetime,数量 int,单价 int,商品ID int)
insert into @in(时间,数量,单价,商品ID)
select 时间,数量,单价,商品ID
from @tbin
where 商品ID in(select 商品ID from @tbout)
order by 商品ID,时间
--创建出库临时表
declare @out table(id int identity(1,1),时间 datetime,数量 int,商品ID int,数量1 int)
insert into @out(时间,数量,商品ID,数量1)
select 时间,数量,商品ID,数量
from @tbout order by 商品ID,时间
while exists (select 1 from @out)
begin
if exists(select 1 from @in)
begin
delete from @up
insert into @up
select i.id,o.id,i.时间
,case when i.数量<o.数量 then i.数量 else o.数量 end
,i.单价,i.商品ID,o.数量1
from
(select * from @in a where id=(select min(id) from @in where 商品ID=a.商品ID)) i
,(select * from @out a where id=(select min(id) from @out where 商品ID=a.商品ID)) o
where i.商品ID=o.商品ID
insert into @result
select 入库时间,入库数量,单价,商品ID,出库数量 from @up
update @in set 数量=数量-b.入库数量
from @in a,@up b where a.id=b.inid
delete from @in where 数量=0
or 商品ID not in(select 商品ID from @out)
update @out set 数量=数量-b.入库数量
from @out a,@up b where a.id=b.outid
delete from @out where 数量=0
end
else
begin
--下面这句是增加出库数量>入库数量的记录,如果不需要,就删除此句
insert into @result(入库时间,数量,商品ID,对应出库)
select 时间,-数量,商品ID,数量1 from @out
--清除出库表,结束处理
delete from @out
end
end
select convert(varchar(10),入库时间,102) as 入库时间
,数量,单价,商品ID,对应出库
from @result order by 商品ID,入库时间
go
[/CODE]