Styjun

I have a hierarchical company structure where each company has an optional company sector. Child companies inherit their parent's sector if they don't have their own, but the child company sector overrides its parent's if the child one.

I need to produce a sum of the bill amount grouped by sector. Companies with no sector are ignored.

The ParentCompanyID column in the Company table is a foreign key to itself. There can be an unlimited number of parent/child levels.

Company Table

CompanyID ParentCompanyID
 1 null
 2 1
 3 2
 4 null
 5 4
 6 null
 7 6
 8 7

CompanySector Table

CompanyID Sector
 1 Distribution
 4 Distribution
 5 Manufacturing
 8 Manufacturing

Timesheet Table

CompanyID BillAmount
 1 100
 1 200
 2 100
 3 50
 4 25
 5 75
 6 75
 7 115
 8 115

The result I expect to see here is:

 Sector BillAmount 
Distribution 475
Manufacturing 190

Below is how I am currently doing it. It is extremely inefficient and doesn't work for unlimited number of hierarchical levels.

WITH Company AS
 ( SELECT 1 CompanyID, NULL ParentCompanyID FROM dual
 UNION
 SELECT 2, 1 FROM dual
 UNION
 SELECT 3, 2 FROM dual
 UNION
 SELECT 4, NULL FROM dual
 UNION
 SELECT 5, 4 FROM dual
 UNION
 SELECT 6, NULL FROM dual
 UNION
 SELECT 7, 6 FROM dual
 UNION
 SELECT 8, 7 FROM dual
 ),
 CompanySector AS
 ( SELECT 1 CompanyID, 'Distribution' Sector FROM dual
 UNION
 SELECT 4 , 'Distribution' FROM dual
 UNION
 SELECT 5 , 'Manufacturing' FROM dual
 UNION
 SELECT 8 , 'Manufacturing' FROM dual
 ),
 Timesheets AS
 ( SELECT 1 CompanyID, 100 BillAmount FROM dual
 UNION
 SELECT 1 CompanyID, 200 BillAmount FROM dual
 UNION
 SELECT 2 CompanyID, 100 BillAmount FROM dual
 UNION
 SELECT 3 CompanyID, 50 BillAmount FROM dual
 UNION
 SELECT 4 CompanyID, 25 BillAmount FROM dual
 UNION
 SELECT 5 CompanyID, 75 BillAmount FROM dual
 UNION
 SELECT 6 CompanyID, 75 BillAmount FROM dual
 UNION
 SELECT 7 CompanyID, 115 BillAmount FROM dual
 UNION
 SELECT 8 CompanyID, 115 BillAmount FROM dual
 ),

 --Dummy tables above
 --My current way of doing it below
 companies AS
 (SELECT c.*,
 cs.sector
 FROM company c
 LEFT OUTER JOIN CompanySector cs
 ON c.companyID = cs.companyID
 ),
 sectors AS
 (SELECT levelOne.companyID,
 NVL(levelOne.sector, NVL(levelTwo.sector, NVL(levelThree.sector, NULL))) sector
 FROM companies levelOne
 LEFT OUTER JOIN companies levelTwo
 ON levelOne.parentcompanyid = levelTwo.companyID
 LEFT OUTER JOIN companies levelThree
 ON levelTwo.parentcompanyid = levelThree.companyID
 WHERE NVL(levelOne.sector, NVL(levelTwo.sector, NVL(levelThree.sector, NULL))) IS NOT NULL
 )
SELECT s.sector,
 SUM(t.billamount)
FROM sectors s
INNER JOIN timesheets t
ON s.companyID = t.companyID
GROUP BY sector;

What is a cleaner and more efficient way of doing this?

I believe this will do it. Using the hierarchical query syntax, populate sector from the parent record where needed.

WITH
 base_sectors AS (
 SELECT * FROM company LEFT OUTER JOIN companySector USING (companyID)
 )
, final_sectors AS (
 SELECT companyID, NVL( sector, PRIOR sector ) AS sector
 FROM base_sectors
 START WITH parentCompanyID IS NULL
 CONNECT BY parentCompanyID = PRIOR companyID
)
SELECT s.sector,
 SUM(t.billamount)
FROM final_sectors s
INNER JOIN timesheets t
ON s.companyID = t.companyID
GROUP BY sector;