Avatar of MohitPandit
MohitPandit
Flag for India asked on

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
Microsoft SQL Server 2008Microsoft SQL Server 2005Microsoft SQL Server

Avatar of undefined
Last Comment
MohitPandit

8/22/2022 - Mon
Saurabh Bhadauria

Can you post here what output you required.  ?  

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

Should there not also be these two rows?
101      A      B      D      B      500
101      A      B      F      B      500
expert_dharam

Need more explanation...
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
MohitPandit

ASKER
I need to put validations either respective booking is valid or not i.e. A needs to pay $3000 to B and the amount mediator receives then the same amount should travel to other mediator and at last the destination i.e. B.

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

Check below code... below query can solve your problem.. you can write it in your way...
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]

Open in new window

Qlemo

Sauv,

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
MohitPandit

ASKER
Hi Saurv,

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
Qlemo

Your original request is still not clear. If you have 30k records, what do you expect as output from them? Are interested in just in something like
Booking	Source	Dest	Total	Remaining	NoMediators
101	A	B	3000	0		7

Open in new window

and then need a query to drill-down to the mediators not having distributed the received money?
SOLUTION
Saurabh Bhadauria

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
MohitPandit

ASKER
Hi Saurv,

I'll take a look over it and let you update after that.
Thanks

Best Regards,
Mohit Sharma
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Mark Wills

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
MohitPandit

ASKER
Thank you very much mark_wills for sharing your great thoughts. I'll try to dig it and let you update after that.
Thanks again!
MohitPandit

ASKER
Excellent!