asked on

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?

ste5an

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

chokka

ASKER

@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

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chokka

ASKER

@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

chokka

ASKER

Incredible !!