Card Rejected - Bank Name - Rahil

set @start1 := '2019-1-01 00:00';
set @start2 := NOW();

Select P.date as MFT_date, P.mid as mid, IFNULL(Q.resv,0) as resv , P.provider as provider , P.bank_name as Bank_name from
(select distinct I.mid as mid, I.date as date , I.provider as provider, I.bank_name as bank_name from
#### Selecting the members whose first payment status is failed
(Select A.mrid as mrid, p.paid_type as paid_type,A.way as way, A.mid as mid , A.date as date , A.state as state , pm.provider as provider, pm.bank_name as bank_name from
#### Finding the first Transaction Details
(select distinct r.member_id as mid, min(r.id) as mrid, date(r.start_at + interval 8 hour) as date , r.state as state, r.way as way
from reservations r left join members m on m.id = r.member_id
where m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
group by 1
order by 1 desc) A
left join payments p on p.reservation_id = A.mrid
left join payment_methods pm on pm.member_id = A.mid
Where A.date >= @start1
and A.date <= @start2
group by 1,2,pm.provider
order by 1 desc) I
where I.state = 'fail'
) P
#### Counting the number of Reservations
left join
(select distinct r.member_id as mid, count(r.id) as resv
from reservations r join members m on m.id = r.member_id
where m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
and r.state in ('completed')
group by 1) Q
on P.mid = Q.mid
where Q.resv is null