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	Dest

ination	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
And, it is only going 1 level deep, it isnt going all the way as if it were a multi-level BOM (hierarchical data). For example, if we are joining on t.[mid destination] to x.[mid source] then why would we expect the t.[mid destination] of 'B' ever match to anything from the x.[mid source] ? So, (if we handle NULL in that case statement), it would report as a mismatch.

Given the original example (with the missing D and F payments to B) then we should see that B has been short paid by 1000 and D and F have both received 500 with no subsequent payments.

qlemo does raise a couple of interesting questions (as well as noting the left join condition)...

If we add in the two additional items (those marked with *) then it all balances just fine, so, what do we want to see ?

We almost have to look at all payments and receipts by individual mediator, and if they pay everything they receive, then we dont have to worry about that mediator.

So, lets look at what happens with NULL handling and then, another approach (using CTE) depending on what you actually want to see :

Open in new window

And, we could probably go down the BOM / hierarchy type approach where you get to see the path of transactions and where the breakdown was, or, some kind of summary level...