need Hierarchical query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

need Hierarchical query

Author

Topic

venkatakondareddy
Starting Member

17 Posts

Posted - 2009-01-06 : 05:16:40

Hi all,
i want to know how to write a hierarchical query with two table. Here im givig two sample tables which i have taken.

Manager table-------------Emp Table
MgrNo---MgrId ----- EmpNo--MgrNo---Sal
1 -- NULL ------ 1 - 2 - 8000
2 -- 1 ------ 2 - 2 - 8000
3 -- 2 ------ 3 - 4 - 7000
4 -- 2 ------ 4 - 10 - 6500
5 -- 3 ------ 5 - 7 - 4000
6 -- 4 ------ 6 - 2 - 7000
7 -- 3 ------ 7 - 2 - 6500
8 -- 5 ------ 8 - 2 - 6500
9 -- 6 ------ 9 - 3 - 6000
10 -- 4 ----- 10 - 5 - 5000
11 -- 8 ------
12 -- 9 ------
13 -- 2 ------

Now i want to sum of sal in the tree of MgrNo=2 and also count of EmpNo's of MgrNo=2 From Emp table and Manager table.
My Expected Output is

MgrNo(Manager table)--MgrId--Sum(sal)--Count(EmpNo). In this format.

Pleae can any one help me.

Regards
Venkat.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-06 : 05:36:25

don't post duplicate it is already post in the new sql server

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-06 : 07:13:30

Use Common Table Expressions To get hierarchy results

Jai Krishna

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-06 : 07:16:25

SELECT MgrNo,SUM(sal),COUNT(EmpNo) FROM Managertable Mt
INNER JOIN EmpTable ON Et(Mt.Mgrno = Et.MgrNo) GROUP BY MgrNo

Jai Krishna

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-06 : 07:58:38

Try This

declare @table table(MgrNo int,MgrId int,empno int ,sal numeric(18,2))
;with cte(MgrNo,MgrId,empno,sal)
as
(
select m.mgrno,m.mgrid,e.empno,e.sal from manager m left join emp e on (m.mgrno = e.mgrno)
where m.mgrid is null

union all

select m.mgrno,m.mgrid,e.empno,e.sal from manager m inner join cte c on (m.mgrid = c.mgrno)
inner join emp e on (m.mgrno = e.mgrno)
)
insert into @table
select distinct * from cte order by mgrno
select mgrno,count(distinct empno) as EmployeeCount,sum(sal) as SalarySum from @table group by mgrno

Jai Krishna

venkatakondareddy
Starting Member

17 Posts

Posted - 2009-01-07 : 00:51:13

Thanks Jai Krishna for your response,
I got output, but not expected.
I got this output
MgrNo-- EmpCount-- salarysum
1 ----- 0 ----- NULL
2 ------ 6 ----- 42000.00
3 ------ 2 ----- 12500.00
4 ------ 2 ----- 12500.00
5 ------ 1 ----- 7000.00
7 ------ 1 ----- 7000.00
10------ 1 ----- 4000.00 This is not expected output.

Expected is
MgrNo-- EmpCount-- Sal
1 -- 10 -- 64500.00
2 -- 10 -- 64500.00
3 -- 3 -- 15000.00
4 -- 2 -- 13500.00
5 -- 1 -- 5000.00
7 -- 1 -- 4000.00
10 -- 1 -- 6500.00
I will explain how 1, 2 and 3 are having, 1 is parent for all so all emps comes under 1, 2 is child for 1 and parent for remaining all, 2 already having 5 EmpNo's directly in emp table and their childs are 3,4,5,7 and 10.
3 is child for 2 and 1, also parent for 5 and 7. Here 3 directly having 1 EmpNo and child EmpNos are 2. sum of sal also same as EmpNo.

Thanks
Venkat

venkatakondareddy
Starting Member

17 Posts

Posted - 2009-01-09 : 04:31:26

hi all, here im giving same as above but tables are differ and also query which i have written.

Asset table1

AssetId AstType ParentAssetId

1 1 NULL
2 2 1
3 3 2
4 4 3
5 4 3
6 4 3
7 4 3
8 5 4
9 7 4
10 5 4
11 6 8
12 6 8
13 6 8
14 6 8
15 8 9
16 8 9
17 9 15
18 9 15
19 7 5
20 7 5
21 7 5
22 7 5
23 5 6
24 5 6
25 5 6
26 5 6
27 7 7
28 5 7
29 5 7
30 10 19
31 10 19
32 10 19
33 10 19
34 10 19
35 10 19
36 10 19
37 10 20
38 10 20
39 10 20
40 10 20
41 10 20
42 10 20
43 10 20
44 10 20
45 10 20
46 10 20
47 10 20
48 10 22
49 10 22
50 10 22
51 10 22
52 10 22
53 8 22
54 10 22
55 10 22
56 8 22
57 10 22
58 10 22
59 10 22
60 10 22
61 8 22
62 10 22
63 10 22
64 10 22
65 6 24
66 6 24
67 6 24
68 6 24
69 6 24
70 6 24
71 6 24
72 6 24
73 6 24
74 6 24
75 10 27
76 10 27
77 10 27
78 8 27
79 10 27
80 10 27
81 10 27
82 10 27
83 10 27
84 10 27
85 10 27
86 10 27
87 10 27
88 10 27
89 10 27
90 10 27
91 10 27
92 10 27
93 6 28
94 6 28
95 6 28
96 6 28
97 6 28
98 6 28
99 6 28
100 6 28
101 6 28
102 6 28
103 6 29
104 6 29
105 6 29
106 9 78
107 9 78
108 9 78
109 3 2
122 3 2
124 4 122
126 4 109
127 5 126
130 4 122
131 4 109
133 3 2
135 4 133
138 4 133
139 3 2
140 4 139
141 4 139
143 16 11
144 16 11
145 16 11
146 16 11
147 12 143
148 12 143
149 13 152
150 13 144
151 16 13
152 16 13
153 16 13
154 16 13
155 16 13
156 13 144
157 13 152
158 4 139
159 4 139

Issue tbale is:

IssueId AssetId

5 2
7 122
12 2
13 2
14 2
15 2
17 138
18 2
19 135
20 2
22 2
25 4
26 8
27 149
31 10
33 140

Now i want to Count of IssueId in the tree of AssetId=2 From Asset table and Issue table.

I will explain how 1, 2 and 3 are having, 1 is parent for all AssetId comes under 1, 2 is child for 1 and parent for remaining all, 2 already having 8 IssueIds directly in Issue table and their childs are 4, 8, 10, 122, 135, 138, 140 and 149. 4 is child for 2 and 1, its having directly one issue, and also parent for 8, 10, 149. like we need to count all issues from IssueTable and which has to map with asset table.

Expected Output is:

AssetId CountOfIssueId
1 16
2 16
3 4
4 4
5 0
6 0
7 0
8 2
9 0
10 1
11 0
12 0
13 1
14 0
15 0
16 0
17 0
18 0
19 0
20 0
21 0
22 0
23 0
24 0
25 0
26 0
27 0
28 0
29 0
30 0
31 0
32 0
33 0
34 0
35 0
36 0
37 0
38 0
39 0
40 0
41 0
42 0
43 0
44 0
45 0
46 0
47 0
48 0
49 0
50 0
51 0
52 0
53 0
54 0
55 0
56 0
57 0
58 0
59 0
60 0
61 0
62 0
63 0
64 0
65 0
66 0
67 0
68 0
69 0
70 0
71 0
72 0
73 0
74 0
75 0
76 0
77 0
78 0
79 0
80 0
81 0
82 0
83 0
84 0
85 0
86 0
87 0
88 0
89 0
90 0
91 0
92 0
93 0
94 0
95 0
96 0
97 0
98 0
99 0
100 0
101 0
102 0
103 0
104 0
105 0
106 0
107 0
108 0
109 0
122 1
124 0
126 0
127 0
130 0
131 0
133 2
135 1
138 1
139 1
140 1
141 0
143 0
144 0
145 0
146 0
147 0
148 0
149 1
150 0
151 0
152 1
153 0
154 0
155 0
156 0
157 0
158 0
159 0

My query is:

WITH REPORTS1(AssetId, ParentAssetId, IssueId)
AS
(
SELECT Asset.AssetId, ParentAssetId, IssueId FROM Asset LEFT OUTER JOIN Issue
ON Asset.AssetId= Issue.AssetId
),
REPORTS2
AS
(
SELECT AssetId AS Asset, AssetId AS Asset2, IssueId FROM REPORTS1 UNION ALL
SELECT R2.Asset, R1.AssetId, R1.IssueId FROM REPORTS2 R2 JOIN REPORTS1 R1 ON
R2.Asset2=R1.ParentAssetId
)
SELECT Asset, COUNT(IssueId) AS CountIssues FROM REPORTS2
GROUP BY Asset;

this query given output is first 2 rows 76,76 and remaining all same.

Please any one can help me..
Thanks
Venkat..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources