Calculate the total weight of segments

I m trying to calculate the total weight of the segments and subsegments in SQL Server 2022 using recursion and i m not even closer to a satisfactory output and maybe you can help me how to adjust the recursive cte or using other method.

I have these 2 tables - segment and segmentpart - with this structure:

CREATE TABLE SEGMENT 
(
    SEGMENTID INT NOT NULL,
    SEGMENTID_FK INT,
    SEGMENTPARTSID INT NOT NULL,
    PRIMARY KEY (SEGMENTID)
);

CREATE TABLE SEGMENTPART 
(
    SEGMENTPARTSID INT NOT NULL,
    SEGMENTTYPEID INT NOT NULL,
    NAME VARCHAR(200) NOT NULL,
    SERIALNUMBER VARCHAR(150),
    WEIGHT DECIMAL(14,4),
    PRIMARY KEY (SEGMENTPARTSID)
);

INSERT INTO SEGMENT VALUES (1, NULL, 1);
INSERT INTO SEGMENT VALUES (2, 1, 2);
INSERT INTO SEGMENT VALUES (3, 1, 3);
INSERT INTO SEGMENT VALUES (4, 2, 4);
INSERT INTO SEGMENT VALUES (5, 2, 5);
INSERT INTO SEGMENT VALUES (6, 3, 6);

INSERT INTO SEGMENTPART VALUES (1, 101, 'Component 1', 'SN001', 10.5);
INSERT INTO SEGMENTPART VALUES (2, 102, 'Component 2', 'SN002', 15.2);
INSERT INTO SEGMENTPART VALUES (3, 103, 'Component 3', 'SN003', 20.3);
INSERT INTO SEGMENTPART VALUES (4, 104, 'Component 4', 'SN004', 8.7);
INSERT INTO SEGMENTPART VALUES (5, 105, 'Component 5', 'SN005', 12.8);
INSERT INTO SEGMENTPART VALUES (6, 106, 'Component 6', 'SN006', 40.8);

This should be the correct output:

SEGMENTID  Total_weight
-----------------------
1             108.3
2              36.7
3              61.1
4               8.7
5              12.8
6              40.8

So for segmentid =1 i need the total for him together with all his subsegments-2,3,4,5,6 so it will be =108.3

for segmentid=2 i need the total for him together with his subsegments-children in our case 4 and 5 so the total will be 36.7 and so on for all.

What i have tried is this but i don t know what to change to make it work:

WITH RCTE AS 
(
    SELECT s1.SEGMENTID, sp.WEIGHT, s1.SEGMENTID_FK
    FROM SEGMENT s1
    INNER JOIN SEGMENTPART sp ON s1.SEGMENTPARTSID = sp.SEGMENTPARTSID
    WHERE s1.SEGMENTID_FK IS NULL

    UNION ALL

    SELECT s2.SEGMENTID, sp.WEIGHT,  s2.SEGMENTID_FK
    FROM SEGMENT s2
    INNER JOIN SEGMENTPART sp ON  s2.SEGMENTPARTSID = sp.SEGMENTPARTSID
    INNER JOIN RCTE ON s2.SEGMENTID_FK = RCTE.SEGMENTID
)
SELECT * FROM rcte

The output is this which is completely wrong:

SEGMENTID     WEIGHT    SEGMENTID_FK
------------------------------------
1             10.5000   NULL
2             15.2000   1
3             20.3000   1
6             40.8000   3
4              8.7000   2
5             12.8000   2

If you need extra info or something is unclear please let me know.

Thank you so much in advance

I used a recursive CTE which starts from the top-level segments (where SEGMENTID_FK is NULL) and then it recursively join to their subsegments based on the SEGMENTID and SEGMENTID_FK relationship. to find child segments for each parent and accumulating the weights.

I try to capture the original segment ID (RootSegmentID) that I started with in the recursion to ensure I can group by it after the recursion completes.

CREATE TABLE SEGMENT (
  SEGMENTID INT NOT NULL,
  SEGMENTID_FK INT,
  SEGMENTPARTSID INT NOT NULL,
  PRIMARY KEY (SEGMENTID)
);

CREATE TABLE SEGMENTPART (
  SEGMENTPARTSID INT NOT NULL,
  SEGMENTTYPEID INT NOT NULL,
  NAME VARCHAR(200) NOT NULL,
  SERIALNUMBER VARCHAR(150),
  WEIGHT DECIMAL(14,4),
  PRIMARY KEY (SEGMENTPARTSID)
);

INSERT INTO SEGMENT VALUES (1, NULL, 1);
INSERT INTO SEGMENT VALUES (2, 1, 2);
INSERT INTO SEGMENT VALUES (3, 1, 3);
INSERT INTO SEGMENT VALUES (4, 2, 4);
INSERT INTO SEGMENT VALUES (5, 2, 5);
INSERT INTO SEGMENT VALUES (6, 3, 6);

INSERT INTO SEGMENTPART VALUES (1, 101, 'Component 1', 'SN001', 10.5);
INSERT INTO SEGMENTPART VALUES (2, 102, 'Component 2', 'SN002', 15.2);
INSERT INTO SEGMENTPART VALUES (3, 103, 'Component 3', 'SN003', 20.3);
INSERT INTO SEGMENTPART VALUES (4, 104, 'Component 4', 'SN004', 8.7);
INSERT INTO SEGMENTPART VALUES (5, 105, 'Component 5', 'SN005', 12.8);
INSERT INTO SEGMENTPART VALUES (6, 106, 'Component 6', 'SN006', 40.8);

WITH RecursiveCTE AS (
    SELECT 
        s.SEGMENTID, 
        s.SEGMENTID AS RootSegmentID, -- You need to keep tracking of the root segment ID
        sp.WEIGHT
    FROM SEGMENT s
    INNER JOIN SEGMENTPART sp ON s.SEGMENTPARTSID = sp.SEGMENTPARTSID
    UNION ALL
    -- Hre is the recursive part: you find and accumulate weights for child segments
    SELECT 
        s.SEGMENTID, 
        r.RootSegmentID, -- It is the same throughout the recursion to track back to the root
        sp.WEIGHT
    FROM SEGMENT s
    INNER JOIN RecursiveCTE r ON s.SEGMENTID_FK = r.SEGMENTID
    INNER JOIN SEGMENTPART sp ON s.SEGMENTPARTSID = sp.SEGMENTPARTSID
)
SELECT 
    RootSegmentID AS SEGMENTID, 
    SUM(WEIGHT) AS Total_weight
FROM RecursiveCTE
GROUP BY RootSegmentID
ORDER BY RootSegmentID;

fiddle