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?
chokkaStudentAsked:
Who is Participating?
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.

ste5anSenior 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];

Open in new window

0
chokkaStudentAuthor 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);

Open in new window

0
ste5anSenior 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];

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
chokkaStudentAuthor 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
0
chokkaStudentAuthor Commented:
Incredible !!
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.