dimanche 28 juin 2015

Working with SQL Hierarchy Syntax

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.

Aucun commentaire:

Enregistrer un commentaire