troubleshooting Question

Hello,

I've a staging table in which I've many rows with respect to booking number.

So, can I do in a single query using CTE to find out below concern? FYI, one way I can use cursor or some iterative loop. But I would like to use in single query using CTE.

--> as per below tables records, booking # 101, A needs to pay $3000 to B; So, first A paid to E = 1000 and A paid to C = 2000 (so that E & C now responsible to pay B).

So, E, C, D, F, X, Y, and Z all our mediator.

Below thing I need to achieve from this table:

1. How many mediator used in this booking and the amount they received from source point; actually they paid all amount to other mediator like A to E 1000 so, from E, it should be paid to other like D & F 500. It is same for C, X, Y & Z?

2. The amount should be delivered completely for each mediator i.e. it should not D = 500 and F = 490.

For e.g., (also, please find attached .png file here)

Note: Booking #, Source & Destination will be duplicate.

Booking#	Source	Destination	Mid Source	Mid Destination	Payment ($)

101	A	B	A	E	1000

101	A	B	A	C	2000

101	A	B	E	D	500

101	A	B	E	F	500

101	A	B	C	X	1000

101	A	B	C	Y	700

101	A	B	C	Z	300

101	A	B	X	B	1000

101	A	B	Y	B	700

101	A	B	Z	B	300

Best Regards,

Mohit Pandit

001-Snap.PNG

I've a staging table in which I've many rows with respect to booking number.

So, can I do in a single query using CTE to find out below concern? FYI, one way I can use cursor or some iterative loop. But I would like to use in single query using CTE.

--> as per below tables records, booking # 101, A needs to pay $3000 to B; So, first A paid to E = 1000 and A paid to C = 2000 (so that E & C now responsible to pay B).

So, E, C, D, F, X, Y, and Z all our mediator.

Below thing I need to achieve from this table:

1. How many mediator used in this booking and the amount they received from source point; actually they paid all amount to other mediator like A to E 1000 so, from E, it should be paid to other like D & F 500. It is same for C, X, Y & Z?

2. The amount should be delivered completely for each mediator i.e. it should not D = 500 and F = 490.

For e.g., (also, please find attached .png file here)

Note: Booking #, Source & Destination will be duplicate.

Booking#	Source	Dest

101	A	B	A	E	

101	A	B	A	C	

101	A	B	E	D	

101	A	B	E	F	

101	A	B	C	X	

101	A	B	C	Y	

101	A	B	C	Z	

101	A	B	X	B	

101	A	B	Y	B	

101	A	B	Z	B	

Best Regards,

Mohit Pandit

001-Snap.PNG

Join the community to see this answer!

Join our exclusive community to see this answer & millions of others.

Unlock 2 Answers and 13 Comments.

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert

See if this solution works for you by signing up for a 7 day free trial.

Unlock 2 Answers and 13 Comments.

Try for 7 days”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.