Help needed in getting total resource count (SQL Server 2005)

Question

I have a requirement. I have been given the following table

DownLineid  UplineId Name DirectResources
1             2        Sarvesh       7
2            NULL     Admin       5
3             2        Lonesh       10
4             2        Swapna       2
5             2        Priyanka        12
8             2         Sumi         1
6             5         Deepak        10
7             5        Agnijita         6
9             5           Me          1

The scenario is UplineId means the Top Managers and the Downlines means the Managers working under the Upliners.

My task is to find out the total resources for the Upliners . The desired output is

UplineId    DownLineid Name DirectResources    TotalResources
NULL          2         Admin          5             54
2   3                    Lonesh     10          null
2   5                     Priyanka 12             17
5   7                        Agnijita 6            null
5   6                        Deepak 10           null
5   9                        Me       1            null
2   1                 Sarvesh           7            null
2   8                 Sumi           1            null
2   4                 Swapna           2            null

Actually , Admin is the top and is having all the resources . so the total count of all the resources is 54.

Agnijita,Deepak and Me is under Priyanka and henceforth the count is 17(6+10+1).

For all other there is no downliners henceforth it is null over there.

My attempt so far is

declare @t table(DownLineid int,UplineId int,Name varchar(10),DirectResources int)
insert into @t
    select 1,2,'Sarvesh',7 union all select 2,Null,'Admin',5 union all
    select 3,2,'Lonesh',10 union all select 4,2,'Swapna',2 union all
    select 5,2,'Priyanka',12 union all select 8,2,'Sumi',1 union all 
    select 6,5,'Deepak',10 union all select 7,5,'Agnijita',6  union all
    select 9,5,'Me',1
select * from @t
;with cte AS
(

    SELECT 
     CAST(e.Name AS VARCHAR(1000)) AS [Path]
     ,e.UplineId
     ,e.DownLineid
     ,e.Name
     ,0 AS [Level]
     ,e.DirectResources FROM @t e WHERE e.UplineId IS NULL 
    UNION ALL

    SELECT CAST(c.[Path] + '/' + e.Name  AS VARCHAR(1000)) AS [Path]
     ,e.UplineId
        ,e.DownLineid
        ,e.Name
        , c.[Level]+1 AS [Level]
        ,e.DirectResources

    FROM @t e 

    JOIN cte c ON e.UplineId = c.DownLineid

)
select 
UplineId
,DownLineid
, REPLICATE('    ', [Level]) + Name as [Name]
,DirectResources

FROM cte 
ORDER BY [Path]

I am struggling to find the total resources. I am using SQL Server 2005.

Please help me.

Thanks in advance.

Answer 1

A self join with a group by and sum clauses looks like the right solution.

Answer 2

I wrote this some time back in my blog

with Manager AS (

select  EmpID,ManID from EmpMan where ManID=1/* assuming that your VP ID is 1, or it can be the top most person whom you want to query on*/

union all

select E.EmpID,E.ManID from EmpMan E

join Manager M on  E.ManID=M.EmpID)

select * from Manager

Answer 3

WITH    q AS
        (
        SELECT  downlineID AS parent, downlineID AS id
        FROM    mytable
        UNION ALL
        SELECT  q.parent, m.downlineID AS id
        FROM    q
        JOIN    mytable m
        ON      m.uplineID = q.id
        )
SELECT  q.parent,
        CASE
        WHEN EXISTS
        (
        SELECT  NULL
        FROM    mytable mi
        WHERE   mi.uplineID = q.parent
        )
        THEN SUM(directResources)
        ELSE NULL
        END AS TotalResources
FROM    q
LEFT JOIN
        mytable m
ON      m.downlineID = q.id
GROUP BY
        q.parent