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
Open in new window
BillingLogic.jpg