Sorry for the delay. Caught up in the work

Following is the query will give you the desire result. in case of any query/ question do let us know.

------------- Create Sample table

create table #try (id int,sourceno int,destno int,quantity int)

------------- Insert data

insert into #try values(1, 100,null,10)

insert into #try values(2, 200,null,10)

insert into #try values(3, 300,200,20)

insert into #try values(4, 400,300,15)

insert into #try values(5, 500,400,40)

insert into #try values(6, 600,null,2)

insert into #try values(7, 700,null,4)

insert into #try values(8, 800,600,6)

;

/************************************************************************************************

Used Jeff moden article "Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets"

URL : http://www.sqlservercentral.com/articles/Hierarchy/94040/

Should read it to understand the problem and the solution

*************************************************************************************************/

with cte

as

(

select a.id, a.sourceno, a.destno, a.quantity

, HLevel = 1

, SortPath = CAST(

CAST(a.sourceno AS BINARY(4))

AS VARBINARY(4000))

from #try a

where a.destno is null

union all

select a.id, a.sourceno, a.destno, a.quantity

, HLevel = cte.HLevel + 1

, SortPath = CAST( --This does the concatenation to build SortPath

cte.SortPath + CAST(a.sourceno AS BINARY(4))

AS VARBINARY(4000))

from #try a

inner join cte on a.destno = cte.sourceno

)

SELECT sourceno = ISNULL(sorted.sourceno,0),

sorted.destno,

HLevel = ISNULL(sorted.HLevel,0),

LeftBower = ISNULL(CAST(0 AS INT),0), --Place holder

RightBower = ISNULL(CAST(0 AS INT),0), --Place holder

NodeNumber = ROW_NUMBER() OVER (ORDER BY sorted.SortPath),

NodeCount = ISNULL(CAST(0 AS INT),0), --Place holder

SortPath = ISNULL(sorted.SortPath,sorted.SortPath),

ID = sorted.id,

quantity = sorted.quantity

INTO #Hierarchy

FROM cte AS sorted

OPTION (MAXRECURSION 100)

UPDATE #Hierarchy

SET LeftBower = 2 * NodeNumber - HLevel

;

with ctally

as

(

SELECT TOP 1000 --(4 * 1000 = VARBINARY(4000) in length)

N = ISNULL(CAST(

(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)*4+1

AS INT),0)

FROM master.sys.all_columns ac1

CROSS JOIN master.sys.all_columns ac2

),

cteCountDownlines AS

( --=== Count each occurrence of EmployeeID in the sort path

SELECT sourceno = CAST(SUBSTRING(h.SortPath,t.N,4) AS INT),

NodeCount = COUNT(*) --Includes current node

FROM #Hierarchy h,

ctally t

WHERE t.N BETWEEN 1 AND DATALENGTH(SortPath)

GROUP BY SUBSTRING(h.SortPath,t.N,4)

) --=== Update the NodeCount and calculate the RightBower

UPDATE h

SET h.NodeCount = downline.NodeCount,

h.RightBower = (downline.NodeCount - 1) * 2 + LeftBower + 1

FROM #Hierarchy h

JOIN cteCountDownlines downline

ON h.sourceno = downline.sourceno

;

----- Once the hierarchies are build following query will do

----- Assuming that you are working on SQL SERVER 2005 and above.

select id, H.sourceno, H.destno, isnull(maxQuantity.quantity, h.quantity)

from #Hierarchy H

inner join

(

Select SUBSTRING(SortPath,1,4) as SortPath, max(HLevel) HLevel

from #Hierarchy H

group by SUBSTRING(SortPath,1,4)

) A on SUBSTRING(H.SortPath,1,4) = A.SortPath and H.HLevel = A.HLevel

Cross apply

(

select max(quantity) as quantity

from #Hierarchy HH

where SUBSTRING(H.SortPath,1,4) = SUBSTRING(HH.SortPath,1,4)

and HH.HLevel < a.HLevel --------- One previous

) maxQuantity

order by id

------ Cleanup

drop table #Hierarchy

drop table #try

Hope it helps.