child-node counting in tree problem

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

General SQL Server Forums

New to SQL Server Programming

child-node counting in tree problem

Author

Topic

sigmas
Posting Yak Master

172 Posts

Posted - 2013-11-04 : 13:25:11

hi,
see:

the query?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-04 : 13:35:40

How is the tree stored in your database? I am visualizing a recursive query that can do the counting, but how one would implement that depends on how the data is stored.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-04 : 13:38:01

How is it implemented in table? Using Heirarchyid datatype or using a recursive CTE?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sigmas
Posting Yak Master

172 Posts

Posted - 2013-11-04 : 13:41:23

child-parent
A null
B A
C A
D B

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-04 : 14:00:47

The code will be in lines of this

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=189388

with a slight tweak to COUNT the child values rather than concatenation
Make a try and revert if you face any issues

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sigmas
Posting Yak Master

172 Posts

Posted - 2013-11-04 : 14:22:16

Is this correct?


declare @emp table
(
EmployeeID int,
BossEmployeeID int
)
 
insert @emp
values
(1, NULL),
(2, 1),
(3, 1),
(4, 2);

With OrgHierarchy
AS
(
SELECT  counting = 0, *        
FROM @emp t
WHERE NOT EXISTS (SELECT 1
FROM @emp WHERE  [BossEmployeeID] = t.[EmployeeID])

UNION ALL

SELECT oh.counting + 1, t.*
FROM OrgHierarchy oh
INNER JOIN @emp t
ON t.[EmployeeID] = oh.[BossEmployeeID]
)

SELECT sum(counting) as counting,
employeeid
FROM OrgHierarchy
group by employeeid
ORDER BY [EmployeeID]
OPTION (MAXRECURSION 0)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-05 : 00:31:19

Why not test it yourself with the full data you've?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sigmas
Posting Yak Master

172 Posts

Posted - 2013-11-05 : 06:58:18

I just want to know that query logicality is correct or not.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-05 : 08:06:23

Did you get your expected result out of the sample data you tried?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources