2

My Table has 3 columns - OriginalDoc, ChildDoc and Fee. For each Parent, Based on their number of Children or GrandChildren - Parent will be charged.

Each Child Doc is charged as $0.40

From the below example,

DocId : AB12456 has one Child Doc and so Fee is $0.40
Doc Id : CY12345 has 7 Children/GrandChildren Doc and so Fee is $2.80

Docs such as NX23410 and NY23422 are not eligible to be charged. Because their Parent Doc # CY12345 is charged for all the children / grand children.

DECLARE @Sample TABLE ( 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);

How to write a Hierarchy SQL Query without Hard Coding any Doc Id's? We will be passing Input Parameter as DocId.

goofyui
  • 3,362
  • 20
  • 72
  • 128
  • I think you're looking for a [recursive CTE](http://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example). See the simple example in that link. – Jeremy Fortune Jun 28 '15 at 23:01
  • Thank you, I am trying with that query samples .. I believe we need Hierarchy Syntax – goofyui Jun 28 '15 at 23:32
  • Yeah, I was going to ask that before, but you're gonna need to give me a link or something to describe what Hierarchy Syntax is.The closest thing I found was [this](https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL), which is just a longer form of the CTE link I gave before. – Jeremy Fortune Jun 28 '15 at 23:37
  • It would be helpful if you explained the output you are looking for as a result of this query? i.e. if you are passing Doc Id : CY12345 the output will be ..... – Charles Jun 29 '15 at 03:48
  • Is the hierarchy fixed at 3 levels (parent, child, grandchild) or can it be any depth? If any depth, does the *charging* apply to all levels? – Damien_The_Unbeliever Jun 29 '15 at 07:25

1 Answers1

3

Here is how you can do this:

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

Fiddle http://sqlfiddle.com/#!3/f61ef/5

Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75