|
楼主 |
发表于 2014-9-9 15:31:51
|
显示全部楼层
if object_id('tempdb..#SSK_CB') is not null drop table #SSK_CB
--declare @x int set @x=1;
declare @ID varchar(50) set @ID ='19010011'; --'物料编码
with t as
(
select 1 as Level ,* from (
SELECT dbo.v_bom_parent_rpt.InvCode AS mjbm, b.cInvName AS mjmc, b.cInvStd AS mjgg,
dbo.v_bom_opcomponent_rpt.BaseQtyN AS jbyl, dbo.v_bom_opcomponent_rpt.BaseQtyD AS jcyl, dbo.v_bom_opcomponent_rpt.InvCode AS zjbm,
a.cInvName AS zjmc, a.cInvStd AS zjgg
FROM dbo.v_bom_parent_rpt INNER JOIN
dbo.v_bom_opcomponent_rpt ON dbo.v_bom_parent_rpt.BomId = dbo.v_bom_opcomponent_rpt.BomId INNER JOIN
dbo.Inventory AS a ON dbo.v_bom_opcomponent_rpt.InvCode = a.cInvCode INNER JOIN
dbo.Inventory AS b ON dbo.v_bom_parent_rpt.InvCode = b.cInvCode
) as ssk_wlqd
WHERE MJBM= @id
union all
SELECT b2.[Level] + 1,B1.* from (
SELECT dbo.v_bom_parent_rpt.InvCode AS mjbm, b.cInvName AS mjmc, b.cInvStd AS mjgg,
dbo.v_bom_opcomponent_rpt.BaseQtyN AS jbyl, dbo.v_bom_opcomponent_rpt.BaseQtyD AS jcyl, dbo.v_bom_opcomponent_rpt.InvCode AS zjbm,
a.cInvName AS zjmc, a.cInvStd AS zjgg
FROM dbo.v_bom_parent_rpt
INNER JOIN dbo.v_bom_opcomponent_rpt ON dbo.v_bom_parent_rpt.BomId = dbo.v_bom_opcomponent_rpt.BomId
INNER JOIN dbo.Inventory AS a ON dbo.v_bom_opcomponent_rpt.InvCode = a.cInvCode
INNER JOIN dbo.Inventory AS b ON dbo.v_bom_parent_rpt.InvCode = b.cInvCode
) as B1 join T AS B2 ON B1.mjbm=B2.zjbm
)
select t_4.*,t_3.iInvSPrice as dj into #SSK_CB from
(
SELECT [Level] ,mjbm ,mjmc ,mjgg,jbyl,jcyl,zjbm,zjmc,zjgg
FROM T
)as t_4
---------------------------------------------------------------------
left join
(
select distinct zjbm,iInvSPrice from ( SELECT * FROM T where zjbm not in (select mjbm from t ))
as t_2 left join
( select cInvCode ,iInvSPrice from inventory )
as t_1 on t_2.zjbm=t_1.cinvcode
) as t_3 on t_3.zjbm=t_4.zjbm
declare @MAX int
set @MAX=(SELECT MAX([LEVEL]) FROM #SSK_CB)
while @MAX >=1
begin
update #SSK_CB set dj=SS.JBDJ
FROM #SSK_CB,
(
SELECT mjbm, isnull(SUM((jbyl/jcyl)*dj),0) AS JBDJ from
(SELECT distinct * FROM #SSK_CB)as SSK_CB_1 WHERE SSK_CB_1.[Level]=@MAX
GROUP BY mjbm)AS SS
WHERE #SSK_CB.Zjbm=SS.mjbm
select @MAX=@MAX-1
end
select * from #SSK_CB --查询明细
SELECT Level ,mjbm ,mjmc,mjgg,SUM(dj) 计划成本 FROM #SSK_CB group by Level ,mjbm ,mjmc,mjgg --查询汇总 |
评分
-
查看全部评分
|