troubleshooting Question

# SQL Server 2008: Query logic using CTE

Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
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#&#9;Source&#9;Destination&#9;Mid Source&#9;Mid Destination&#9;Payment (\$)
101&#9;A&#9;B&#9;A&#9;E&#9;1000
101&#9;A&#9;B&#9;A&#9;C&#9;2000
101&#9;A&#9;B&#9;E&#9;D&#9;500
101&#9;A&#9;B&#9;E&#9;F&#9;500
101&#9;A&#9;B&#9;C&#9;X&#9;1000
101&#9;A&#9;B&#9;C&#9;Y&#9;700
101&#9;A&#9;B&#9;C&#9;Z&#9;300
101&#9;A&#9;B&#9;X&#9;B&#9;1000
101&#9;A&#9;B&#9;Y&#9;B&#9;700
101&#9;A&#9;B&#9;Z&#9;B&#9;300

Best Regards,
Mohit Pandit
001-Snap.PNG
Join the community to see this answer!
###### Learn from the best

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.