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.