?
Solved

SQL Server 2008: Query logic using CTE

Posted on 2012-12-21
14
Medium Priority
?
531 Views
Last Modified: 2012-12-26
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
0
Comment
Question by:MohitPandit
  • 5
  • 3
  • 2
  • +3
13 Comments
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38715030
Can you post here what output you required.  ?  

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

Expert Comment

by:awking00
ID: 38715728
Should there not also be these two rows?
101      A      B      D      B      500
101      A      B      F      B      500
0
 
LVL 3

Expert Comment

by:expert_dharam
ID: 38716022
Need more explanation...
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 5

Author Comment

by:MohitPandit
ID: 38716397
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.
0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38718078
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

0
 
LVL 71

Expert Comment

by:Qlemo
ID: 38718103
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.
0
 
LVL 5

Author Comment

by:MohitPandit
ID: 38720101
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
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 38720181
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?
0
 
LVL 12

Assisted Solution

by:Saurabh Bhadauria
Saurabh Bhadauria earned 600 total points
ID: 38720804
Since you have 30K records then better would be to use a temp table to store a  grouped sum records..
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

Open in new window

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

Open in new window

0
 
LVL 5

Author Comment

by:MohitPandit
ID: 38720868
Hi Saurv,

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

Best Regards,
Mohit Sharma
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 1000 total points
ID: 38721336
Ummm... with the left join there can be NULL where there is no payment from a mediator (ie they have received, but not handed on) and the code is not checking the NULL condition...

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 :

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',      500   --*

-- let's put in a bit of NULL handling in the CASE statement

select t.*, case when x.payment is NULL or x.payment<>t.Payment then 'Payment Mistmatch ' else '' end  as [status] , x.payment as source_payments
from @ta t 
left 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]

-- now lets try to match up on payments and receipts by mediator

;with cte_rec as
(
  select booking, source, destination, case when [mid source] = source then '' else [mid source] end as mediator, payment * -1 as payment
  from @ta 

  union all

  select booking, source, destination, case when [mid destination] = destination then '' else [mid destination] end, payment 
  from @ta 
) 
select booking, source, destination, mediator, sum(payment) as balance
from cte_rec
-- where mediator > ''   -- taken out to see total still owed to destination
group by booking, source, destination, mediator
having sum(payment) <> 0  

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...
0
 
LVL 5

Author Comment

by:MohitPandit
ID: 38722387
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!
0
 
LVL 5

Author Closing Comment

by:MohitPandit
ID: 38722426
Excellent!
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question