I want this share calculation for MLM of Total Price(36000)Here.

See more:

SQL

server

TreeTable
ID Name PID Price
1 100 0 3000.00
2 101 1 3000.00
3 102 1 3000.00
4 103 1 3000.00
5 104 2 3000.00
6 105 3 3000.00
7 106 4 3000.00
8 107 5 3000.00
9 108 6 3000.00
10 109 7 3000.00
11 110 8 3000.00
12 111 9 3000.00

Create table TreeTable(ID int primary key not null,NAME varchar(20),PID int,Price Money);
Insert into TreeTable Values(1,’100’,0,3000);
Insert into TreeTable Values(2,’101’,1,3000);
Insert into TreeTable Values(3,’102’,1,3000);
Insert into TreeTable Values(4,’103’,1,3000);
Insert into TreeTable Values(5,’104’,2,3000);
Insert into TreeTable Values(6,’105’,3,3000);
Insert into TreeTable Values(7,’106’,4,3000);
Insert into TreeTable Values(8,’107’,5,3000);
Insert into TreeTable Values(9,’108’,6,3000);
Insert into TreeTable Values(10,’109’,7,3000);
Insert into TreeTable Values(11,’110’,8,3000);
Insert into TreeTable Values(12,’111’,9,3000);

I want that My share percentage should increase Tree_Level Wise 1% of total sum of Price starting from 10 %.
Output Should Like
ID Name PID Price Tree Level Share(%) Amount
1 100 0 3000.0000 0 10 3600
2 101 1 3000.0000 1 11 3960
3 102 1 3000.0000 1 11 3960
4 103 1 3000.0000 1 11 3960
5 104 2 3000.0000 2 12 4320
6 105 3 3000.0000 2 12 4320
7 106 4 3000.0000 2 12 4320
8 107 5 3000.0000 3 13 4680
9 108 6 3000.0000 3 13 4680
10 109 7 3000.0000 3 13 4680
11 110 8 3000.0000 4 14 5040
12 111 9 3000.0000 4 14 5040
Total Price: 36000.0000
I have done Like This…

WITH Tree_CTE(ID,name, PID,price,Tree_level)
AS
(
    SELECT TreeTable.*, 0 FROM TreeTable WHERE PID =0 
    UNION ALL
    SELECT ChildNode.*, ch.Tree_level+1
	  FROM TreeTable AS ChildNode
    INNER JOIN Tree_CTE ch 
    ON ChildNode.PID = ch.ID
)
SELECT ID,name, PID,price,Tree_level FROM Tree_CTE  order by Tree_level 

Output
ID name PID price Tree_level
1 100 0 3000.00 0
2 101 1 3000.00 1
3 102 1 3000.00 1
4 103 1 3000.00 1
7 106 4 3000.00 2
6 105 3 3000.00 2
5 104 2 3000.00 2
8 107 5 3000.00 3
9 108 6 3000.00 3
10 109 7 3000.00 3
12 111 9 3000.00 4
11 110 8 3000.00 4

PLZ Help me..