I mean a sample output dataset as per above data.... and also some more explanation....
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.
Connect with top rated Experts
17 Experts available now in Live!