[易飞]库存分类情况表

/*
*用途：库存分类情况表
*作者：龚德辉 2011-02-13
*/

CREATE proc UP_InvItemPriceChart
as
begin

Select a.MC001 品号,b.MB002 品名,b.MB003 规格,b.MB004 单位,c.MC002 仓库,a.MC007 库存数量,a.MC008 库存金额,
case when left(b.MB003,2)='AZ' then '继电器'
when left(b.MB003,2)='ZE' then '贸易品'
when left(a.MC001,1)='1' then '成品'
when left(b.MB001,1)<>'1' THEN '原材料'

END AS 种类,
case when left(b.MB003,2)='AZ' then '继电器'
when left(b.MB003,2)='ZE' then '贸易品'
when left(a.MC001,1)='1' then '成品'
when left(b.MB001,1)<>'1' and left(a.MC001,3)='301' then 'LCD'
when left(b.MB001,1)<>'1' and left(a.MC001,3)='302' then 'PCB'
when left(b.MB001,1)<>'1' and left(a.MC001,3)='303' then '背光'
when left(b.MB001,1)<>'1' and left(a.MC001,3)='304' then '导电胶条'
when left(b.MB001,1)<>'1' and left(a.MC001,3)='305' then '铁框'
when left(b.MB001,1)<>'1' and left(a.MC001,3)='306' then 'IC'
when left(b.MB001,1)<>'1' and left(a.MC001,3)='307' then '连接件'
else '辅料' END as 小类
into #tmp
from INVMC a inner join CMSMC c on a.MC002=c.MC001 inner join INVMB b on a.MC001=b.MB001
where a.MC007>0

select 种类,sum(库存金额) 库存金额 from #tmp
group by 种类

select 小类,sum (库存金额) 库存金额 from #tmp
where 种类='原材料'
group by 小类

drop table #tmp

end
GO