In SQL Server I have a table named task with columns: task_id, task_name, task_weight, task_parent. the task_parent column is a foreign key which may be null or be related to another task_id in this table. I want to write a CTE to show tasks in a sheet like MS Project where task_weight of atask row with some recursive children, is summarized from its children and level of each child is calculated based on its recursive parents. Each child may have another child and so on. So consider this to calculate top level weight.
the schema is like this:
CREATE TABLE TASK
(
task_id int IDENTITY(1,1) PRIMARY KEY,
task_name varchar(100),
task_weight float,
task_parent int
);
And the data is like this:
SET IDENTITY_INSERT [task] ON;
INSERT INTO task (task_id,task_name,task_weight,task_parent) VALUES
(1,'t1',null,null),
(2,'t2',null,1),
(3,'t3',0.03,1),
(4,'t4',0.04,2),
(5,'t5',0.03,2),
(6,'t6',null,null),
(7,'t7',0.01,6);
SET IDENTITY_INSERT [task] OFF;
I expect a result like this (consider the order please):
task_name | task_weight | Level |
---|---|---|
t1 | 0.10 | 1 |
__t2 | 0.07 | 2 |
____t4 | 0.04 | 3 |
____t5 | 0.03 | 3 |
__t3 | 0.03 | 2 |
t6 | 0.01 | 1 |
__t7 | 0.01 | 2 |