Thank you Amit.
We use a 5 layer process, instead of the traditional medallion bronze, silver, gold. The materialised views are split into two functions instead of just gold, where we have 'calculated' and 'aggregated'. Once we have performed any calculations we need we than pass it over to the aggregated views to roll up a company level hierarchy. We were hoping to use a recursive CTE as a template to roll everything up in the same way each time, like the query below.
Our company hierarchy table (screenshot attached) contains the parent child relationship but the parent Id is the row Id of the parent not the Id of the parent team itself. In the screenshot all the child teams are related to the top row.
-----------------------------------------------------------------
-- PREPARE SUBSET OF DATA
-----------------------------------------------------------------
DECLARE
@StartDate int = 20240715,
@EndDate int = 20240721;
DECLARE
@Workload TABLE(
[TeamId] int,
[DateId] int,
[Duration] int
)
INSERT INTO
@Workload
SELECT
[w].[WorkgroupId],
[w].[DateId],
[w].[Duration]
FROM
[Workload] w
WHERE
[w].[Verified] = 1
AND [w].[DateId] BETWEEN @StartDate AND @EndDate;
-----------------------------------------------------------------
-- BUILD HIERARCHY
-----------------------------------------------------------------
WITH [Hierarchy] (
[TopRowId],
[RowId],
[TeamId],
[TopWorkgroupId])
AS (
SELECT
[ch].[Id],
[ch].[Id],
[ch].[TeamId],
[ch].[TeamId]
FROM
[CompanyHierarchy] ch
WHERE
[ch].[EndDate] IS NULL
AND ([ch].[RootNodeId] = 1 OR [ch].[TeamId] = 1)
UNION ALL
SELECT
[h].[TopRowId],
[wh].[Id],
[wh].[WorkgroupId],
[tr].[WorkgroupId]
FROM
[Hierarchy] h
JOIN [CompanyHierarchy] ch
ON [h].[RowId] = [ch].[ParentId]
JOIN [CompanyHierarchy] tr
ON [h].[TopRowId] = [tr].[Id]
WHERE
[ch].[EndDate] IS NULL
)
-----------------------------------------------------------------
-- JOIN DATA TO HIERARCHY AND ROLL UP
-----------------------------------------------------------------
SELECT
[h].[TopWorkgroupId],
[t].[Name] AS [Team],
[d].[DateId],
SUM(CAST(ISNULL([d].[Duration] / 60.0,0.0) AS decimal(10,2))) AS [Amount accumulated]
FROM
[Hierarchy] h
JOIN @Workload w
ON [h].[WorkgroupId] = [w].[TeamId]
JOIN [Teams] t
ON [h].[TopWorkgroupId] = [t].[Id]
GROUP BY
[h].[TopWorkgroupId],
[t].[Name],
[w].[DateId]
ORDER BY
[t].[Name],
[w].[DateId]
-----------------------------------------------------------------
-----------------------------------------------------------------
