This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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

I mean a sample output dataset as per above data.... and also some more explanation....

Let's take A (source) paid E (mediator) 1000 then E paid to D & F (mediator) 500 each.

--> in that case E receives 1000 and same amount he paid to E & D i.e. 500 each.

I apologize, two more records should be in table; added in below table in last (marked with *).

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

101 A B D B 500 *

101 A B F B 500 *

Similar A paid to C and C paid to X,Y, & Z and X,Y and Z paid to B. So, I need to put validations the amount given by source/mid source then it should be same travel at destination/mid destination otherwise I need to put an alert to end user.

I hope, it should clear now. Kindly let me know in case still not clear.

even you can use CTE if you are working with multiple sources and destinations...

```
Declare @ta table (Booking int, Source char(2) , Destination char(2) , [Mid Source] char(2) , [Mid Destination] char(2) , Payment int)
insert into @ta
select 101 , 'A' ,'B', 'A' , 'E' ,1000
union all select 101 , 'A' ,'B', 'A' ,'C' , 2000
union all select 101 , 'A' ,'B', 'E' ,'D' , 500
union all select 101 , 'A' ,'B', 'E' ,'F' , 500
union all select 101 , 'A' ,'B', 'C' ,'X' , 1000
union all select 101 , 'A' ,'B', 'C' ,'Y' , 700
union all select 101 , 'A' ,'B', 'C' ,'Z', 300
union all select 101 , 'A' ,'B', 'X' ,'B', 1000
union all select 101 , 'A' ,'B', 'Y' ,'B', 700
union all select 101 , 'A' ,'B', 'Z' ,'B', 300
union all select 101 , 'A' ,'B', 'D' ,'B', 500
union all select 101 , 'A' ,'B', 'F' ,'B', 50
select * from @ta
select t.*, case when x.payment<>t.Payment then 'Payment Mistmatch ' else '' end as [status] from @ta t join
(select booking,Source,Destination,[Mid Source] , SUM(Payment) as payment from @ta
group by booking,Source,Destination,[Mid Source]) x
on t.Booking=x.booking and t.Source=x.Source and t.Destination=x.Destination and t.[Mid Destination]=x.[Mid Source]
```

I had prepared something similar. It might suffice, but for one point - if a transaction is completely missing, it does not appear. E.g. if E made no transfer, A -> E with 1000 will not appear. Using a Left Join (and applying isnull to x.payment) should help.

Additionally I would also output the missing amount.

I checked the code and looks good. Actually, I can have 30k records in a file so it would good if we will use CTE. Could you please share your thoughts?

I also noted Qlemo point for using LEFT OUTER JOIN.

Thanks

Best Regards,

Mohit Pandit

```
Booking Source Dest Total Remaining NoMediators
101 A B 3000 0 7
```

and then need a query to drill-down to the mediators not having distributed the received money?
```
Declare @ta table (Booking int, Source char(2) , Destination char(2) , [Mid Source] char(2) , [Mid Destination] char(2) , Payment int)
Create table #temp_group
(
Booking int,
Source char(2),
Destination char(2),
[Mid Source] char(2) ,
payement_sum bigint
)
insert into @ta
select 101 , 'A' ,'B', 'A' , 'E' ,1000
union all select 101 , 'A' ,'B', 'A' ,'C' , 2000
union all select 101 , 'A' ,'B', 'E' ,'D' , 500
union all select 101 , 'A' ,'B', 'E' ,'F' , 500
union all select 101 , 'A' ,'B', 'C' ,'X' , 1000
union all select 101 , 'A' ,'B', 'C' ,'Y' , 700
union all select 101 , 'A' ,'B', 'C' ,'Z', 300
union all select 101 , 'A' ,'B', 'X' ,'B', 1000
union all select 101 , 'A' ,'B', 'Y' ,'B', 700
union all select 101 , 'A' ,'B', 'Z' ,'B', 300
union all select 101 , 'A' ,'B', 'D' ,'B', 500
union all select 101 , 'A' ,'B', 'F' ,'B', 50
union all select 101 , 'A1' ,'B1', 'A1' , 'E1' ,2000
union all select 101 , 'A1' ,'B1', 'A1' ,'C1' , 4000
union all select 101 , 'A1' ,'B1', 'E1' ,'D1' , 1000
union all select 101 , 'A1' ,'B1', 'E1' ,'F1' , 1000
union all select 101 , 'A1' ,'B1', 'C1' ,'X1' , 2000
union all select 101 , 'A1' ,'B1', 'C1' ,'Y1' , 1400
union all select 101 , 'A1' ,'B1', 'C1' ,'Z1', 600
union all select 101 , 'A1' ,'B1', 'X1' ,'B1', 2000
union all select 101 , 'A1' ,'B1', 'Y1' ,'B1', 1400
union all select 101 , 'A1' ,'B1', 'Z1' ,'B1', 600
union all select 101 , 'A1' ,'B1', 'D1' ,'B1', 1000
union all select 101 , 'A1' ,'B1', 'F1' ,'B1', 00
insert into #temp_group
select booking,Source,Destination,[Mid Source] , SUM(Payment) as payment from @ta
group by booking,Source,Destination,[Mid Source]
select t.*, case when x.payement_sum<>t.Payment then 'Payment Mistmatch ' else '' end as [status] from @ta t left join #temp_group x
on t.Booking=x.booking and t.Source=x.Source and t.Destination=x.Destination and t.[Mid Destination]=x.[Mid Source]
drop table #temp_group
```

The last select query in above code will return all records with payment status , if you just want to result set with payment mismatch..then you can move case statement condition to where ..like this...

```
select t.* from @ta t left join #temp_group x
on t.Booking=x.booking and t.Source=x.Source and t.Destination=x.Destination and t.[Mid Destination]=x.[Mid Source]
where x.payement_sum<>t.Payment
```

Thanks again!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

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...