--这样?
--> 测试数据: @s
declare @s table (月份 varchar(7),产品型号 varchar(1),本月出库 int,本月入库 int,原始库存 int)
insert into @s
select '2008-06','A',1000,2000,1500 union all
select '2008-06','A',1000,2000,1500 union all
select '2008-06','C',1000,2000,1500 union all
select '2008-07','A',1000,2000,1500 union all
select '2008-07','B',1000,2000,1500
select 月份,
上月库存=(select isnull(sum(原始库存),0)+isnull(sum(本月入库),0)-isnull(sum(本月出库),0) from @s where datediff(mm,月份+'-01',a.月份+'-01')=1
and 产品型号=a.产品型号),
产品型号,
本月出库=sum(本月出库),
本月入库=sum(本月入库),
上月库存=sum(原始库存)+sum(本月入库)-sum(本月出库)
from @s a
group by 月份,产品型号
建议写成为样,出入库一个表,
当前库存一个表,历史库存一个表,
然后就是查库存异动记录了,这个程序有点难啊,请在程序里用内存计算完成。我至今还没有看到一个将库存台账查询做得很漂亮的;
比如查5月11是到今天的台账,那这个就要经5月11时那个点为期初了,呵呵,很难写的哦,
Transfer表
PartNo,Num(+/-),TranDate,StockHis表
PartNO,InitStock,OutNum,InNum,AdjustNum,EndStockInitStock从上月抓吧,OutNum,InNum,AdjustNum从本月计算过来,
EndStock=InitStock+InNum-OutNum+AdjustNum
我设计得很简单,呵呵,
declare @temp table(月份 varchar(10),产品型号 varchar(10),上月库存 int,本月出库 int,本月入库 int,原始库存 int,本月库存 int)
insert into @temp
select * from (
select '2008-06' 月份 , 'A' 产品型号,0 上月库存, 1000 本月出库, 2000 本月入库, 1500 原始库存,0 本月库存
union all select '2008-07', 'A',0, 1000, 500, 0 ,0
union all select '2008-08', 'A',0, 1000, 1000, 0,0
) t order by 1
declare @t1 int,@t2 int
--set @t=0--(select top 1 原始库存 from @temp)
update @temp set 本月库存=@t1,上月库存=@t2,
@t1=case when @t1 is null then 原始库存 else @t1-本月出库+本月入库 end,
@t2=case when @t2 is null then @t1 else @t1+本月出库-本月入库 end
from @temp
select * from @temp
/*
2008-06 A 1500 1000 2000 1500 1500
2008-07 A 1500 1000 500 0 1000
2008-08 A 1000 1000 1000 0 1000
*/
insert into @temp
select 月份,产品型号,sum(上月库存),sum(本月出库),sum(本月入库),sum(原始库存),sum(本月库存) from (
select '2008-06' 月份 , 'A' 产品型号,0 上月库存, 1000 本月出库, 2000 本月入库, 1500 原始库存,0 本月库存
union all select '2008-06', 'A',0, 1000, 2000, 1500 ,0
union all select '2008-06', 'C',0, 1000, 2000, 1500,0
union all select '2008-07', 'A',0, 1000, 2000, 0 ,0
union all select '2008-07', 'B',0, 1000, 2000, 1500,0
) t group by 月份,产品型号 order by 1,2
declare @t1 int,@t2 int
declare @c varchar(10)
declare c cursor for select distinct 产品型号 from @temp
open c
fetch c into @c
while @@fetch_status=0 begin
update @temp set 本月库存=@t2-本月出库+本月入库,上月库存=@t2,
@t1=case when @t1 is null then 原始库存 else @t1-本月出库+本月入库 end,
@t2=case when @t2 is null then @t1 else @t1+本月出库-本月入库 end
from @temp where 产品型号=@c
set @t1=null
set @t2=null
fetch c into @c
end
close c
deallocate c
select * from @temp/*
2008-06 A 3000 2000 4000 3000 5000
2008-06 C 1500 1000 2000 1500 2500
2008-07 A 3000 1000 2000 0 4000
2008-07 B 1500 1000 2000 1500 2500*/