# Complex SQL Query with Cursor

I have to write a SQL Stored Procedure by passing DOC Id as Input parameter and has to calculate the fee for each parent doc.

In my Document Table, I have Original Doc and Child Doc. For every doc, I pass  as an Input parameter for my stored procedure, I have to find whether the Doc is a Child Doc or Parent Doc and if it is child, I have update the fee as \$0.40.

Based on my example,

On passing AB12456 it has only one child and so billing is \$0.40.
On passing CY12345, It has 3 child and one of its child became parent with two children. And one of its grandchild became parent and has 2 children. So this Parent Doc should be billed as \$2.80.

Original Doc       Child Doc       Fee      Expected Fee
AB12456              NX12450             \$0.40
CY12345         NX23410
CY12345              NX23421
CY12345        NX23432
NX23410      NY23411
NX23410      NY23422
NY23422      NZ23411
NY23422      NZ23422             \$2.80

I have to write a logic without hard coding and calculate, how much each parent doc is billed. Amount has to be updated in the Fee Column.

How should i do this?
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Senior DeveloperCommented:
hmm, e.g.

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

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

WITH Hierarchy AS
(
SELECT	*,
A.OriginalDoc AS RootDoc,
1 AS [Level],
'\\' + CAST(A.OriginalDoc AS VARCHAR(MAX)) AS [Path]
FROM	@Sample A
WHERE	A.OriginalDoc IN ( 'AB12456', 'CY12345' )
UNION ALL
SELECT	C.*,
P.RootDoc,
P.[Level] + 1,
P.[Path] + '\' + C.OriginalDoc
FROM	@Sample C
INNER JOIN Hierarchy P ON P.ChildDoc = C.OriginalDoc
),
MaxLevel AS
(
SELECT	MAX(H.[Level]) AS MaxLevel
FROM	Hierarchy H
),
Calc AS
(
SELECT	*,
COALESCE(A.Fee, 0) AS SumFee
FROM	Hierarchy A
WHERE	A.[Level] = (
SELECT	MaxLevel
FROM	MaxLevel
)
UNION ALL
SELECT	P.*,
COALESCE(C.SumFee,0) + COALESCE(P.Fee, 0)
FROM	Calc C
INNER JOIN Hierarchy P ON P.ChildDoc = C.OriginalDoc
)
SELECT	*
FROM	Calc
ORDER BY [Level], [Path];
``````
StudentAuthor Commented:
@ste5an .. Great Help !! But the output is incorrect.. Hierarchy is great syntax .. which i wasn't aware.

In the Below Insert Query , We have 3 columns - Original Doc,  Child Doc and Fee.  Fee is the column we have to set it.

Doc Id : AB12456 has one child doc ... So it has a Fee of \$0.40
and Doc Id : CY12345 has 7 chidren ( Including sub-children ).. so it has a fee of \$2.80

We are setting the fee for one child as \$0.40. Based on that we calculate the children fee.

``````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);
``````
Senior DeveloperCommented:
Well, MaxLevel was not quite correct:

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

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

WITH    Hierarchy
AS ( SELECT   * ,
A.OriginalDoc AS RootDoc ,
1 AS [Level] ,
'\\' + CAST(A.OriginalDoc AS VARCHAR(MAX)) AS [Path]
FROM     @Sample A
WHERE    A.OriginalDoc IN ( 'AB12456', 'CY12345' )
UNION ALL
SELECT   C.* ,
P.RootDoc ,
P.[Level] + 1 ,
P.[Path] + '\' + C.OriginalDoc
FROM     @Sample C
INNER JOIN Hierarchy P ON P.ChildDoc = C.OriginalDoc
),
MaxLevel
AS ( SELECT   H.RootDoc ,
MAX(H.[Level]) AS MaxLevel
FROM     Hierarchy H
GROUP BY H.RootDoc
),
Calc
AS ( SELECT   A.* ,
COALESCE(A.Fee, 0) AS SumFee
FROM     Hierarchy A
INNER JOIN MaxLevel ML ON ML.RootDoc = A.RootDoc
AND ML.MaxLevel = A.[Level]
UNION ALL
SELECT   P.* ,
COALESCE(C.SumFee, 0) + COALESCE(P.Fee, 0)
FROM     Calc C
INNER JOIN Hierarchy P ON P.ChildDoc = C.OriginalDoc
)
SELECT  *
FROM    Calc
ORDER BY [Level] ,
[Path];
``````

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

StudentAuthor Commented:
@ste5an, I am so thankful.

I got an opportunity to learn the Hierarchy Syntax. I am aware about Hierarchy Connect By Syntax in Oracle.

This is so great.

Thanks
StudentAuthor Commented:
Incredible !!
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.