if exists (select 1 from sysobjects where name ='pro_Bom_AllSum' and type = 'p' )
begin

drop procedure pro_bom_Allsum

/* 说明：订单材料统计表，两订单查询范围，一个小范围，一个大范围

注意：小范围订单一定要在大范围内，否则计算正确 ，如 小范围 A0904-012到A0904-023 ，大范围则 A0904-001到A0904-030
产生表结构：物料编号，物料名称，小范围用量，大范围用量，单位
格式： exec pro_bom_AllSum 小范围开始订单号，小范围结束订单号 ，大范围开始订单号，大范围结束订单号
Author:Sam
Build Date: 2009-04-16
Update date:
State: Finished
*/

create procedure pro_Bom_AllSum
( @startOdrNo varchar(20)='%',
@endOdrNo varchar(20)='%' ,
@StartOdrNo_out varchar(20)='%',
@endOdrNo_out varchar(20)='%'
)
as
begin

/* 产生 ＃Tmp_bom_Sum 临时表 ，订单小范围 */
if exists (select name from sysobjects where name = '#tmp_bom_Sum' and type ='u')
drop table #tmp_bom_Sum
/*
declare @startOdrNo varchar(20)
declare @endOdrNo varchar(20)
set @startOdrNo='a0904-003'
set @endOdrNo='a0904-004'
*/
select matNo,sum(mat_qty) as mat_Qty,kcdw into #tmp_bom_Sum from
(
select matNo,sum(mat_tqty) as Mat_Qty,kcdw from odr_sampleBom
where left(odr_no,9) between @StartOdrNo AND @EndOdrNo
GROUP BY MatNo,kcdw
union all
select a.matNO_jg,sum(a.plan_qty),b.kcdw from odr_sampleBom_jg a, odr_sampleBom b
where a.flowNo=b.flowNo and ( left(a.odr_no,9) between @StartOdrNo AND @EndOdrNo )
GROUP BY a.matNo_jg,b.kcdw
) dd group by matnO,kcdw

/* 产生 ＃Tmp_bom_AllSum 临时表 ，订单大范围 */
if exists (select name from sysobjects where name = '#tmp_bom_AllSum' and type ='u')
drop table #tmp_bom_AllSum

select matNo,sum(mat_qty) as mat_Qty,kcdw into #tmp_bom_AllSum from
(
select matNo,sum(mat_tqty) as Mat_Qty,kcdw from odr_sampleBom
where left(odr_no,9) between @StartOdrNo_out AND @EndOdrNo_out
GROUP BY MatNo,kcdw
union all
select a.matNO_jg,sum(a.plan_qty),b.kcdw from odr_sampleBom_jg a, odr_sampleBom b
where a.flowNo=b.flowNo and ( left(a.odr_no,9) between @StartOdrNo_out AND @EndOdrNo_out )
GROUP BY a.matNo_jg,b.kcdw
) tt group by tt.matnO,tt.kcdw

/*注意：小范围订单一定要在大范围内，否则计算正确 */
select tm.matNo,mat.mat_nm ,tm.Small_mat_qty,tm.big_mat_qty,tm.kcdw from
(
select #tmp_bom_AllSum.matNO,#tmp_bom_Sum.mat_qty AS Small_Mat_Qty, #tmp_bom_AllSum.mat_qty as big_Mat_Qty,#tmp_bom_AllSum.kcdw from #tmp_bom_AllSum
left outer join #tmp_bom_sum on #tmp_bom_AllSum.matNo=#tmp_bom_Sum.matNo and #tmp_bom_AllSum.kcdw=#tmp_bom_Sum.kcdw
) tm ,mat_material mat where tm.matno=mat.matno
order by tm.matNo

end

end

exec pro_bom_sum 'A0904-001','A0904-002'

exec pro_bom_sum 'A0904-003','A0904-004'

exec pro_bom_sum 'A0904-001','A0904-004'

EXEC pro_bom_Allsum 'A0904-001','A0904-010','A0904-001','A0904-024'

select * from tmp_bom_allSum ORDER BY MATnO -- (A0904-001~004)

select * from tmp_bom_Sum ORDER BY MATnO --(A0904-001~002)

select * from tmp_bom_Sum2 ORDER BY MATnO --(A0904-003~004)