Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.
Become a Premium Member and unlock a new, free course in leading technologies each month.
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
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]
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 ..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
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
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.