Rolling up hierarchical costs

• vaughan_davies

  Valued Member

  Points: 63

  More actions

  April 27, 2009 at 1:10 am

  #208876

  I'm trying to roll up costs from our bill of labour file. I'm simplifying, but the data is arranged something like this:

  assembly_code component_code quantity_used

  000001 000011 1

  000001 000012 2

  000001 000013 1

  000002 000011 1

  000002 000013 2

  000011 000013 1

  000011 000014 6

  000012 000011 1

  ...

  The assembly_code and the component_code are both product codes. The same component_code can also be an assembly_code (and vice versa obviously). Thus there can be a multiple level hierarchy of product codes. The application prevents loops (where e.g. an assembly contains a component, which in turn contains the original assembly as a component).

  The costs are contained in a separate table like this:

  product_code labour_cost

  000001 50

  000002 10

  000011 40

  000012 30

  000013 25

  000014 2

  ...

  I can easily see how to use an aggregate SUM function to total the cost from the next layer down, but I can't see how to reliably get below that. For example, if I know that the greatest depth we have is five layers I can write a query that joins five copies of the bill of labour table, assembly_code to component_code. But if I then try to aggregate the level 2 component costs, any such level 2 component that has mutiple level 3 or below components will be added in multiple times.

  I'm sure this must be a fairly routine query for an expert, but I'm really struggling with it. Anyone have any suggestions?

  Thanks in advance

  Vaughan

• Jeff Moden

  SSC Guru

  Points: 1004638

  More actions

  May 3, 2009 at 6:55 pm

  #987892

  Vaughan,

  I noticed zero replies on your post... did you eventually get this or do you still need help?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  May 3, 2009 at 7:17 pm

  #987894

  SQL Serve 2005 supports ANSI standard recursive common table element SQL that will explode the hierarchy. Below is the CTE solution and a solution if the RDBMS does not support this functionallity.

  ALTER PROCEDURE dbo.OrgUnitAdjacency_Expand_CTE

  ( @OrgUnitIdBIGINT

  , @OrgLevelDepthinteger

  )

  AS

  SET NOCOUNT ON

  SET XACT_ABORT ON

  ;WITH DirectReports (OrgUnitId_Parent, OrgUnitId, OrgLevelDepth, OrgUnitName)

  AS

  (

  -- Anchor member definition

  SELECTOrgUnitAdjacency.OrgUnitId_Parent

  ,OrgUnitAdjacency.OrgUnitId

  ,0as OrgLevelDepth

  ,OrgUnitAdjacency.OrgUnitName

  FROMdbo.OrgUnitAdjacency

  WHEREOrgUnitAdjacency.OrgUnitId = @OrgUnitId

  UNION ALL

  SELECTOrgUnitAdjacency.OrgUnitId_Parent

  ,OrgUnitAdjacency.OrgUnitId

  ,DirectReports.OrgLevelDepth + 1

  ,OrgUnitAdjacency.OrgUnitName

  FROMdbo.OrgUnitAdjacency

  JOINDirectReports

  ON OrgUnitAdjacency.OrgUnitId_Parent = DirectReports.OrgUnitId

  WHEREOrgUnitAdjacency.OrgUnitId OrgUnitAdjacency.OrgUnitId_Parent

  ANDDirectReports.OrgLevelDepth < @OrgLevelDepth

  )

  -- Statement that executes the CTE

  SELECTDirectReports.OrgLevelDepth

  ,DirectReports.OrgUnitId

  ,DirectReports.OrgUnitId_Parent

  ,DirectReports.OrgUnitName

  FROMDirectReports

  --ORDER BY DirectReports.OrgLevelDepth ,DirectReports.OrgUnitId

  go

  Pre-CTE SQL:

  CREATE PROCEDURE dbo.OrgUnitAdjacency_Expand_Stack

  ( @OrgUnitIdBIGINT

  , @OrgLevelDepthinteger

  )

  AS

  SET NOCOUNT ON

  SET XACT_ABORT ON

  DECLARE @OrgLevelDepth_Current int

  ,@OrgUnitChildCntint

  SET@OrgLevelDepth_Current= 0

  SET@OrgUnitChildCnt = 1

  CREATE TABLE #OrgUnitStack

  ( OrgUnitIdBIGINTnot null

  , OrgUnitId_ParentBIGINTnot null

  , OrgLevelDepthintegernot null

  , OrgUnitNamevarchar(255) not null

  )

  INSERT INTO #OrgUnitStack

  ( OrgUnitId

  , OrgLevelDepth

  , OrgUnitName

  , OrgUnitId_Parent

  )

  selectOrgUnitAdjacency.OrgUnitId

  ,@OrgLevelDepth_Current

  ,OrgUnitAdjacency.OrgUnitName

  ,OrgUnitAdjacency.OrgUnitId_Parent

  FROMdbo.OrgUnitAdjacency

  WHEREdbo.OrgUnitAdjacency.OrgUnitId = @OrgUnitId

  IF@@ROWCOUNT = 0RETURN 0

  WHILE@OrgLevelDepth_Current 0

  BEGIN

  SET@OrgLevelDepth_Current = @OrgLevelDepth_Current + 1

  INSERT INTO #OrgUnitStack

  ( OrgUnitId

  , OrgLevelDepth

  , OrgUnitName

  , OrgUnitId_Parent

  )

  selectOrgUnitAdjacency.OrgUnitId

  ,@OrgLevelDepth_Current

  ,OrgUnitAdjacency.OrgUnitName

  ,OrgUnitAdjacency.OrgUnitId_Parent

  FROM#OrgUnitStack

  JOINdbo.OrgUnitAdjacency

  onOrgUnitAdjacency.OrgUnitId_Parent= #OrgUnitStack.OrgUnitId

  WHERE#OrgUnitStack.OrgLevelDepth= @OrgLevelDepth_Current - 1

  ANDOrgUnitAdjacency.OrgUnitId_Parent OrgUnitAdjacency.OrgUnitId

  set@OrgUnitChildCnt = @@Rowcount

  END

  select@OrgUnitIdas OrgUnitId_Root

  ,#OrgUnitStack.OrgLevelDepth

  ,#OrgUnitStack.OrgUnitId

  ,#OrgUnitStack.OrgUnitId_Parent

  ,#OrgUnitStack.OrgUnitName

  from#OrgUnitStack

  --ORDER BY #OrgUnitStack.OrgLevelDepth,#OrgUnitStack.OrgUnitId

  GO

  SQL = Scarcely Qualifies as a Language