<div class="codebody" id="code40814">
--------
Create table stock
(Id int not null primary key,
articleno varchar(20) not null,
rcvdate datetime not null,
qty int not null,
unitprice money not null
)
go
----
insert stock
select 1,'10561122','2011-1-1',15,10 union
select 2,'2011-2-2',25,12 union
select 3,'2011-3-3',35,15 union
select 4,'2011-4-4',45,20 union
select 5,'2011-5-5',55,10 union
select 6,'2011-6-6',65,30 union
select 7,'2011-7-7',75,17 union
select 8,'2011-8-8',110,8 go
----此时如果在2011-8-8卖出300件产品，那么应该如何计算库存销售的价值呢？
----1使用当前的替换成本，2011-8-8时每件产品的成本为8，就是说你这300件产品，成本价值为2400
----2使用当前的平均成本单价，一共有420，总成本为6530，平均每件的成本为15.55
----1.LIFO （后进先出）
----2011-8-8 110 8
----2011-7-7 7517
----2011-6-6 6530
----2011-5-5 5010
-----总成本为 4605
-----2.FIFO(先进先出)
---- '2011-1-1',1510
--- '2011-2-2',2512
-----'2011-3-3',3515
-----'2011-4-4',4520
-----'2011-5-5',5510
-----'2011-6-6',6530
-----'2011-7-7',6517
----总成本为5480 ---成本视图
create view costLIFO
as
select unitprice from stock
where rcvdate= (select MAX(rcvdate) from stock)
go
create view costFIFO
as
select sum(unitpriceqty)/SUM(qty) as unitprice from stock go
-----找出满足订单的、足够存货的最近日期。如果运气好的话，某一天的库存数量正好与订单要求的数字完全一样
-----就可以将总成本作为答案返回。如果订单止的数量比库存的多，什么也不返回。如果某一天的库存数量比订单数量多
---则看一下当前的单价，乘以多出来的数量，并减去它。
---下面这些查询和视图只是告诉我们库存商品的库存价值，注意，这些查询与视图并没有实际从库存中向外发货。
create view LIFO
as
select s1.rcvdate,s1.unitprice,sum(s2.qty) as qty,sum(s2.qtys2.unitprice) as totalcost
from stock s1,stock s2
where s2.rcvdate>=s1.rcvdate
group by s1.rcvdate,s1.unitprice go
select (totalcost-((qty-300)unitprice )) as cost
from lifo as l
where rcvdate=(select max(rcvdate) from lifo as l2 where qty>=300)
go create view FIFO
as
select s1.rcvdate,stock s2
where s2.rcvdate<=s1.rcvdate
group by s1.rcvdate,s1.unitprice go
select (totalcost-((qty-300)unitprice )) as cost
from fifo as l
where rcvdate=(select min(rcvdate) from lifo as l2 where qty>=300)
--------
go
-----
-----在发货之后，实时更新库存表
create view CurrStock
as
select s1.rcvdate,SUM(case when s2.rcvdate>s1.rcvdate then s2.qty else 0 end) as PrvQty
,SUM(case when s2.rcvdate<=s1.rcvdate then s2.qty else 0 end) as CurrQty
from stock s1,s1.unitprice
go
create proc RemoveQty
@orderqty int
as
if(@orderqty>0)
begin
update stock set qty =case when @orderqty>=(select currqty from CurrStock as c where c.rcvdate=stock.rcvdate)
then 0
when @orderqty<(select prvqty from CurrStock c2 where c2.rcvdate=stock.rcvdate)
then stock.qty
else (select currqty from CurrStock as c3 where c3.rcvdate=stock.rcvdate)
-@orderqty end
end
--
delete from stock where qty=0
---
go
exec RemoveQty 20
go
---------------