We help IT Professionals succeed at work.
Get Started

Syntax issue in my SQL Query

1 Endorsement
Last Modified: 2015-07-02
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 );

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

Watch Question
Contract ERP Admin/Consultant
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE