--汇总追加成本
select csa.AccountLevel ,
csa.AccountShortName ,
csa.StageAccountGUID ,
csa.ParentStageAccountGUID ,
csa.IsEndCost ,
csa.ProjGUID ,
csa.HierarchyCode ,
isnull(t.AdjustAmount,0) as AdjustAmount
into #data
from cb_StageAccount csa
left join (
select ctcsvd.StageAccountGUID,
sum(isnull(cad.AdjustAmount,0)) as AdjustAmount
from cb_AdjustDtl cad
inner join cb_Adjust ca on ca.AdjustGUID = cad.AdjustGUID
inner join cb_TargetCostStageVersionDetail ctcsvd on cad.TargetStage2CostGUID = ctcsvd.TargetCostStageVersionDetailGUID
--inner join 过滤,只取最新目标版本数据
inner join #ctcvPX cp on cp.TargetCostStageVersionGUID = ctcsvd.TargetCostStageVersionGUID
where ca.AdjustType = '追加目标成本'
group by ctcsvd.StageAccountGUID
)t on csa.StageAccountGUID = t.StageAccountGUID
where csa.ProjGUID in (@ProjGUID)
--更新父级
declare @MaxLevel int;
declare @CurrentLevel int;
select @MaxLevel =max(AccountLevel) from #data ;
set @CurrentLevel = @MaxLevel
while @CurrentLevel >= 0
begin
update a
SET a.AdjustAmount = ISNULL((SELECT SUM(b.AdjustAmount) FROM #data b WHERE b.ParentStageAccountGUID=a.StageAccountGUID and b.ProjGUID = a.ProjGUID),a.AdjustAmount)
from #data a
where a.AccountLevel = @CurrentLevel
set @CurrentLevel = @CurrentLevel - 1
end
;
--存在层级的时候,需要按照各级一次网上汇总的时候使用,而不是单一的sum