February 17, 2014 at 5:37 am
Hi experts,
i've following data in a recursive query:
Par_Id ID A B C D E F Level
Null 022646 0 0 0 1 0 0 022646\
022646 022426 3 2 0 0 0 0 022646\022426\
022646 022532 3 2 1 0 0 0 022646\022532\
022646 022540 8 2 0 0 0 0 022646\022540\
022646 022557 5 1 1 0 0 0 022646\022557\
022646 022650 8 2 0 0 0 0 022646\022650\
022646 022737 3 1 1 0 0 0 022646\022737\
022646 023683 0 1 0 0 0 0 022646\023683\
023683 024089 6 0 0 0 0 0 022646\023683\024089\
023683 024488 1 3 1 0 0 0 022646\023683\024488\
023683 024863 9 1 4 0 0 0 022646\023683\024863\
These are the aspected results.
Par_Id ID A B C D E F Level
Null 22646 0 0 0 1 0 0 022646\
G_Total 62 19 13 1 0 0 GrandTotal
22646 22426 3 2 0 0 0 0 022646\022426\
22646 22532 3 2 1 0 0 0 022646\022532\
22646 22540 8 2 0 0 0 0 022646\022540\
22646 22557 5 1 1 0 0 0 022646\022557\
22646 22650 8 2 0 0 0 0 022646\022650\
22646 22737 3 1 1 0 0 0 022646\022737\
22646 23683 0 1 0 0 0 0 022646\023683\
23683 24089 6 0 0 0 0 0 022646\023683\024089\
23683 24488 1 3 1 0 0 0 022646\023683\024488\
23683 24863 9 1 4 0 0 0 022646\023683\024863\
23683 Subtot 16 5 5 0 0 0 Subtotal - 23683
in few words i need subtotal only for who have children.
I tried with rollup but i wasn't able to have it similar to the aspected.
I put it the level just to let clearer the dependencies...
Thanks in advance
Tony
February 17, 2014 at 5:46 am
this link will help you to get subtotals and grand totals
http://www.sqlusa.com/bestpractices2005/subtotaltotalgrandtotal/
February 20, 2014 at 6:26 am
You could expand the hierarchy (i.e. create a direct relationship between each node and all of its parents) and then run your queries against the expanded hierarchy.
This example does something along these lines, it might be of use as a starting point.
-------------------------------
-- create a temporary hierarchy
-------------------------------
create table #Hierarchy (HierarchyId int, ParentId int)
insert #Hierarchy select 1,5
insert #Hierarchy select 2,5
insert #Hierarchy select 3,6
insert #Hierarchy select 4,6
insert #Hierarchy select 5,7
insert #Hierarchy select 6,7
insert #Hierarchy select 7,8
insert #Hierarchy select 8,null
alter table #Hierarchy add constraint pk_H primary key clustered (HierarchyId)
-----------------------------------
-- create a temporary "sales" table
-----------------------------------
create table #sales (Id int, A int, B int, C int)
insert #sales values (1,10,4,4)
insert #sales values (1,12,3,9)
insert #sales values (1,7,30,0)
insert #sales values (2,6,21,3)
insert #sales values (3,1,5,6)
insert #sales values (4,13,3,8)
insert #sales values (5,4,4,5)
---------------------------------
-- create a temporary tally table
---------------------------------
-- we'll use this later to expand the hierarchy
-- count in fours as this is all we need to split the binary identifiers
if isnull(object_id('tempdb..#QuadTally'),0) <> 0
begin
drop table #QuadTally
end
; with e1(n) as (
select 1 union all select 1 union all select 1 union all
select 1 union all select 1 union all select 1 union all
select 1 union all select 1 union all select 1 union all
select 1
)
, e2(n) as (select 1 from e1 a cross join e1 b) -- 1*10^2 or 100 rows - more than enough for our purposes
select top (100) n = isnull(cast((row_number() over (order by (select null))-1) * 4 + 1 as int),0)
into #QuadTally
from e2
-- add clustered index to the temporary tally table
alter table #QuadTally add constraint pk_qt primary key clustered (n)
---------------------------------------------------------------
-- create a temporary table with all hierarchical relationships
---------------------------------------------------------------
/*
The following cascading cte creates a direct column to column relationship
for each member of the hierarchy with both itself and all of its upline records
This allows very simple rollup of totals
*/
; with cteExpandedHierarchy as ( -- use a recursive cte to expand the hierarchy
select ParentId
, HierarchyId -- ANCHOR
, SortPath = cast(cast(HierarchyId as binary(4)) as varbinary(4000))
, Lv = 1
from #Hierarchy anchor
where ParentId is null
union all
select recurse.ParentId
, recurse.HierarchyId -- CHILD NODES
, SortPath = cast(cte.SortPath + cast(recurse.HierarchyId as binary(4)) as varbinary(4000)) -- concatenate HierarchyIds into the SortPath column
, Lv = cte.Lv + 1
from cteExpandedHierarchy cte
join #Hierarchy recurse
on cte.HierarchyId = recurse.ParentId
)
, cteSplit as ( -- expand the SortPath to create a direct relationship between each member and all of their parents
select dval.HierarchyId
, SuperiorId = cast(substring(dval.SortPath,qt.n,4) as int)
, Lv
from dbo.#QuadTally qt
cross join cteExpandedHierarchy dval
where qt.n between 1 and datalength(sortpath)
)
select *
into #ExpandedHierarchy
from cteSplit
-- show the effect of joining to the table
select *
from #ExpandedHierarchy eh
join #sales s
on eh.HierarchyId = s.Id
-- use the expanded hierarchy to get totals for each node's sales + children's sales
-- where HierarchyId = SuperiorId, this is the actual value of the sales table for that member of the hierarchy
select SuperiorId
, sum(A) as sumA
, sum(B) as sumB
, sum(C) as sumC
from #ExpandedHierarchy eh
join #sales s
on eh.HierarchyId = s.Id
group by SuperiorId
-- use the expanded hierarchy to get totals for each node with children - children's sales only at any level
select SuperiorId
, sum(case when HierarchyId = SuperiorId then 0 else A end) as sumA
, sum(case when HierarchyId = SuperiorId then 0 else B end) as sumB
, sum(case when HierarchyId = SuperiorId then 0 else C end) as sumC
from #ExpandedHierarchy eh
join #sales s
on eh.HierarchyId = s.Id
group by SuperiorId
This borrows fairly heavily from Jeff Moden's "Hierarchies on Steroids", which I'd recommend reading if you haven't already:
http://qa.sqlservercentral.com/articles/T-SQL/94570/
Regards, Iain
February 20, 2014 at 8:50 am
Tony,
Can you provide some DDL (table definition and insert statements)?
One way to accomplish this is traversing one level at a time in a procedural way. You can start from the leave nodes and using a temporary table to hold intermediate calculations. This approach tend to be the one with better performance.
Another way is using the transitive closure of the DAG. This wait you can have a combination of each node with itself and all its subordinates, then you can join this to the sales table and group by the parent side.
Transitive Closure
http://sqlmag.com/t-sql/transitive-closure
Calculate the Sum of Salaries in an Employee Tree
http://sqlmag.com/t-sql/calculate-sum-salaries-employee-tree
Thanks to "irebertson" for providing sample data.
SET NOCOUNT ON;
USE tempdb;
GO
create table #Hierarchy (HierarchyId int NOT NULL, ParentId int)
insert #Hierarchy select 1,5
insert #Hierarchy select 2,5
insert #Hierarchy select 3,6
insert #Hierarchy select 4,6
insert #Hierarchy select 5,7
insert #Hierarchy select 6,7
insert #Hierarchy select 7,8
insert #Hierarchy select 8,null
alter table #Hierarchy add constraint pk_H primary key clustered (HierarchyId)
-----------------------------------
-- create a temporary "sales" table
-----------------------------------
create table #sales (Id int, A int, B int, C int)
insert #sales values (1,10,4,4)
insert #sales values (1,12,3,9)
insert #sales values (1,7,30,0)
insert #sales values (2,6,21,3)
insert #sales values (3,1,5,6)
insert #sales values (4,13,3,8)
insert #sales values (5,4,4,5)
GO
WITH TC AS (
SELECT
HierarchyId AS PKey,
HierarchyId AS SKey
FROM
#Hierarchy AS H1
WHERE
EXISTS (
SELECT
*
FROM
#Hierarchy AS H2
WHERE
H2.ParentId = H1.HierarchyId
)
UNION ALL
SELECT
P.PKey,
C.HierarchyId AS SKey
FROM
TC AS P
INNER JOIN
#Hierarchy AS C
ON C.ParentId = P.SKey
)
SELECT
PKey,
SUM(A) AS sum_A,
SUM(B) AS sum_B,
SUM(C) AS sum_C
FROM
TC
INNER JOIN
#sales AS S
ON S.Id = TC.SKey
GROUP BY
TC.PKey
GO
DROP TABLE #Hierarchy, #sales;
GO
February 20, 2014 at 9:32 am
Here other example data and how i solved it (in different way because wasn't able to have GrandTotal and SubTotal as i wanted)
declare @TestData table(
[Par_Id] [varchar](6) NULL,
[ID] [varchar](6) NULL,
[A] [int] NULL,
[int] NULL,
[C] [int] NULL,
[D] [int] NULL,
[E] [int] NULL,
[F] [int] NULL,
[Level] [HierarchyId] NULL
)
INSERT INTO @TestData([Par_Id], [ID], [A], , [C], [D], [E], [F], [Level])
SELECT NULL, 22646, 0, 0, 0, 1, 0, 0, N'/1/' UNION ALL
SELECT 22646, 22426, 3, 2, 0, 0, 0, 0, N'/1/2/' UNION ALL
SELECT 22646, 22532, 3, 2, 1, 0, 0, 0, N'/1/3/' UNION ALL
SELECT 22646, 22540, 8, 2, 0, 0, 0, 0, N'/1/4/' UNION ALL
SELECT 22646, 22557, 5, 1, 1, 0, 0, 0, N'/1/5/' UNION ALL
SELECT 22646, 22650, 8, 2, 0, 0, 0, 0, N'/1/6/' UNION ALL
SELECT 22646, 22737, 3, 1, 1, 0, 0, 0, N'/1/7/' UNION ALL
SELECT 22646, 23683, 0, 1, 0, 0, 0, 0, N'/1/8/' UNION ALL
SELECT 23683, 24089, 6, 0, 0, 0, 0, 0, N'/1/8/1/' UNION ALL
SELECT 23683, 24488, 1, 3, 1, 0, 0, 0, N'/1/8/2/' UNION ALL
SELECT 23683, 24863, 9, 1, 4, 0, 0, 0, N'/1/8/3/'
select Level.ToString(), *, Level.GetLevel() as [Level],
( select Sum( A )
from @TestData where Level.IsDescendantOf( P.Level ) = 1 ) as [TotalA],
( select Sum( B )
from @TestData where Level.IsDescendantOf( P.Level ) = 1 ) as [TotalB],
( select Sum( C )
from @TestData where Level.IsDescendantOf( P.Level ) = 1 ) as [TotalC],
( select Sum( D )
from @TestData where Level.IsDescendantOf( P.Level ) = 1 ) as [TotalD],
( select Sum( E )
from @TestData where Level.IsDescendantOf( P.Level ) = 1 ) as [TotalE],
( select Sum( F )
from @TestData where Level.IsDescendantOf( P.Level ) = 1 ) as [TotalF]
from @TestData as P order by 1
i'll appreciate any suggestions
Thanks again
February 20, 2014 at 10:06 am
Tony,
Thanks for providing the sample data. It makes our life easier. 🙂
Here is the example using the transitive closure but as I said this is not the best solution performance wise.
SET NOCOUNT ON;
USE tempdb;
GO
declare @TestData table(
[Par_Id] [varchar](6) NULL,
[ID] [varchar](6) NULL,
[A] [int] NULL,
[int] NULL,
[C] [int] NULL,
[D] [int] NULL,
[E] [int] NULL,
[F] [int] NULL,
[Level] [HierarchyId] NULL
)
INSERT INTO @TestData([Par_Id], [ID], [A], , [C], [D], [E], [F], [Level])
SELECT NULL, 22646, 0, 0, 0, 1, 0, 0, N'/1/' UNION ALL
SELECT 22646, 22426, 3, 2, 0, 0, 0, 0, N'/1/2/' UNION ALL
SELECT 22646, 22532, 3, 2, 1, 0, 0, 0, N'/1/3/' UNION ALL
SELECT 22646, 22540, 8, 2, 0, 0, 0, 0, N'/1/4/' UNION ALL
SELECT 22646, 22557, 5, 1, 1, 0, 0, 0, N'/1/5/' UNION ALL
SELECT 22646, 22650, 8, 2, 0, 0, 0, 0, N'/1/6/' UNION ALL
SELECT 22646, 22737, 3, 1, 1, 0, 0, 0, N'/1/7/' UNION ALL
SELECT 22646, 23683, 0, 1, 0, 0, 0, 0, N'/1/8/' UNION ALL
SELECT 23683, 24089, 6, 0, 0, 0, 0, 0, N'/1/8/1/' UNION ALL
SELECT 23683, 24488, 1, 3, 1, 0, 0, 0, N'/1/8/2/' UNION ALL
SELECT 23683, 24863, 9, 1, 4, 0, 0, 0, N'/1/8/3/';
WITH TC AS (
SELECT
[ID] AS PKey,
[ID] AS SKey
FROM
@TestData AS H1
WHERE
EXISTS (
SELECT
*
FROM
@TestData AS H2
WHERE
H2.[Par_Id] = H1.[ID]
)
UNION ALL
SELECT
P.PKey,
C.[ID] AS SKey
FROM
TC AS P
INNER JOIN
@TestData AS C
ON C.[Par_Id] = P.SKey
)
, Agg AS (
SELECT
PKey,
SUM(A) AS sum_A,
SUM(B) AS sum_B,
SUM(C) AS sum_C,
SUM(D) AS sum_D,
SUM(E) AS sum_E,
SUM(F) AS sum_F
FROM
TC
INNER JOIN
@TestData AS S
ON S.[ID] = TC.SKey
GROUP BY
TC.PKey
)
SELECT
A.Level.ToString() AS MPath,
A.*,
A.Level.GetLevel() as [Level],
ISNULL(B.sum_A, A.A) AS TotalA,
ISNULL(B.sum_B, A.B) AS TotalB,
ISNULL(B.sum_C, A.C) AS TotalC,
ISNULL(B.sum_D, A.D) AS TotalD,
ISNULL(B.sum_E, A.E) AS TotalE,
ISNULL(B.sum_F, A.F) AS TotalF
FROM
@TestData AS A
LEFT OUTER JOIN
Agg AS B
ON A.[ID] = B.PKey
ORDER BY
MPath;
GO
February 20, 2014 at 10:48 am
Since you already has a hierarchyid type column in your sample, may be using methods from this type will do it for you.
SELECT
A.Level.ToString() AS MPath,
A.*,
A.Level.GetLevel() as [Level],
ISNULL(C.sum_A, A.A) AS TotalA,
ISNULL(C.sum_B, A.B) AS TotalB,
ISNULL(C.sum_C, A.C) AS TotalC,
ISNULL(C.sum_D, A.D) AS TotalD,
ISNULL(C.sum_E, A.E) AS TotalE,
ISNULL(C.sum_F, A.F) AS TotalF
FROM
@TestData AS A
OUTER APPLY
(
SELECT
SUM(B.A) AS sum_A,
SUM(B.B) AS sum_B,
SUM(B.C) AS sum_C,
SUM(B.D) AS sum_D,
SUM(B.E) AS sum_E,
SUM(B.F) AS sum_F
FROM
@TestData AS B
WHERE
B.[Level].IsDescendantOf(A.[Level]) = 1
) AS C
ORDER BY
MPath;
GO
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply