Syntax issue in my SQL Query

chokka
chokka used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Contract ERP Admin/Consultant
Commented:
I made a working solution using a temporary table:

DECLARE @Sample TABLE (
    OriginalDoc         VARCHAR(255),
    ChildDoc            VARCHAR(255),
    Fee                 MONEY,
    IsChildBecameParent VARCHAR(5) );

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

UPDATE  @Sample
SET     Fee = 0,
        IsChildBecameParent = 'TRUE'
WHERE   ChildDoc IN (SELECT OriginalDoc FROM @Sample)

UPDATE  @Sample
SET     Fee = 0.4,
        IsChildBecameParent = 'FALSE'
WHERE   ChildDoc NOT IN (SELECT OriginalDoc FROM @Sample)

SELECT * FROM @Sample
ORDER BY 1, 2

Open in new window

chokkaStudent

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial