SQL Server 2008: Query logic using CTE

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