关于先进先出成本计算2

declare @mingx table
(id    int,    商品代码    varchar(5),   数量    int,    价格   int,      日期 datetime,        类别 varchar(10))
insert into @mingx select 1,          '001',            10,           8,        '2007-03-01',     '采购'
union all select 2,          '001',             8,          12,        '2007-03-10',     '采购'
union all select 3,          '001',            12,           6,        '2007-04-15',     '采购'
union all select 4,          '001',            12,           6,        '2007-04-15',     '采购'
union all select 5,          '001',             2,          16,        '2007-04-05',     '销售'
union all select 6,          '001',            18,          16,        '2007-04-20',     '销售'
union all select 7,          '001',            2,           10,        '2007-05-15',     '销售'
union all select 8,          '002',            12,           6,        '2007-04-15',     '采购'
union all select 9,          '002',            12,           6,        '2007-04-15',     '销售'
union all select 10,          '003',            12,           6,        '2007-03-15',     '采购'
union all select 11,          '003',            12,           6,        '2007-04-15',     '采购'
union all select 12,          '003',            10,           16,        '2007-05-15',     '销售'

select tmp2.商品代码,
[日期]=convert(varchar(6),tmp2.日期,112),
[成本金额]=sum((case when sum_销售<sum_采购 then sum_销售 else sum_采购 end
-
case when sum_销售-tmp2.数量<sum_采购-tmp1.数量 then sum_采购-tmp1.数量 else sum_销售-tmp2.数量 end)
*tmp1.价格)
from
(select *,
sum_采购=(select sum(数量) from @mingx where 类别=t1.类别 and 商品代码=t1.商品代码 and id!>t1.id)
from @mingx t1 where 类别='采购')tmp1
join
(select *,
sum_销售=(select sum(数量) from @mingx where 类别=t2.类别 and 商品代码=t2.商品代码 and id!>t2.id)
from @mingx t2 where 类别='销售')tmp2
on
tmp1.商品代码=tmp2.商品代码
where sum_销售-tmp2.数量<sum_采购 and sum_采购-tmp1.数量<sum_销售

and convert(varchar(6),tmp2.日期,112)=200704--取4月

group by tmp2.商品代码,convert(varchar(6),tmp2.日期,112)