Syntax issue in my SQL Query

My query post the fee to the Parent Doc based on number of child doc. But i need to skip the billed child which has been already billed.

Expected output from my code snippet would be

Parent Doc      BilledChildDoc      Fee      IsChildBecameParent

AB12456       NX12450              0.4      FALSE
CY12345        NX23410                      0      TRUE
CY12345        NX23421                      0.4      FALSE
CY12345        NX23432                      0.4      FALSE
NX23410       NY23411                     0.4      FALSE
NX23410          NY23422                 0      TRUE
NY23422         NZ23411                   0.4      FALSE
NY23422       NZ23422              0.4      FALSE

Create table Sample ( OriginalDoc VARCHAR(255), ChildDoc VARCHAR(255), Fee MONEY );

INSERT INTO Sample
VALUES  ( 'AB12456', 'NX12450', NULL ),
    ( 'CY12345', 'NX23410', NULL ),             
    ( 'CY12345', 'NX23421', NULL ),             
    ( 'CY12345', 'NX23432', NULL ),             
    ( 'NX23410', 'NY23411', NULL ),             
    ( 'NX23410', 'NY23422', NULL ),             
    ( 'NY23422', 'NZ23411', NULL ),             
    ( 'NY23422', 'NZ23422',NULL);



with cte as(select *, OriginalDoc as Parent from Sample s1 
            where not exists(select * from Sample s2 where s2.ChildDoc = s1.OriginalDoc)
           
            union all
            
            select s.*, c.Parent from cte c
            join Sample s on c.ChildDoc = s.OriginalDoc
           )
            
select Parent, count(*)*0.4 as Fee from cte
group by Parent

Select all Open in new window

BillingLogic.jpg