One way Trip - Airport - Hoon and Rahil _ V2.0

Select A.mid as memberid,
B.rid as rid,
B.way as way,
B.reservationdate as res_date,
Date(C.MFTDate) as MFTdate,
date(C.lastactivedate) as lastactivedate,
IFNULL(ROUND(D.rev,2),0) as gross_amount,
ROUND(IFNULL(E.coupon,0),1) as coupon_amount,
Round((IFNULL(D.rev,0) - IFNULL(E.coupon,0)),2) as net_rev
from
(select fb.mid
from
(select
distinct r.member_id as mid, min(r.id) as rid, r.way as way, m.email as email, r.start_zone_id as stid, r.end_zone_id as edid, r.state as state , Date(r.return_at + interval 8 hour) as date
from reservations r
join members m on m.id = r.member_id
where r.state = 'completed' and m.state = 'normal' and m.imaginary = 'normal' and m.email not like '%socar.my%'
group by r.member_id) fb
where fb.way in ('oneway','onewayReturn') and fb.email not like '%socar.my%' and fb.stid in ('244','245') or fb.edid in ('244','245') and fb.date > '2019-06-01') A
join
(select
m.id as memberid,
r.id as rid,
r.way as way,
Date(r.start_at + interval '8' hour) as reservationdate
from  members m
left outer join reservations r on m.id = r.member_id
Where r.start_at != 0 and m.imaginary = 'normal' and r.state = 'completed'
group by m.id,r.id) B on B.memberid = A.mid
left join
(select
m.id as memberid,
TIMESTAMPDIFF(YEAR, str_to_date(m.birthday, '%Y%m%d'), Now()) AS age,
min(r.start_at + interval '8' hour) as MFTdate,
Max(r.start_at + interval '8' hour) as lastactivedate
from  members m
left outer join reservations r on m.id = r.member_id
join charges c on m.id = c.member_id
Where r.start_at != 0
and m.imaginary = 'normal'
and c.kind = 'subscriptionFee'
and r.state = 'completed'
group by m.id) C  on B.memberid = C.memberid
left join
(select sum(c.amount) as rev, c.reservation_id as rid
from charges c
where c.state = 'normal' and c.kind IN ('rent' , 'oneway', 'd2d', 'mileage')
group by c.reservation_id) as D ON B.rid = D.rid
left join
(select IFNULL(p.amount,0) as coupon, p.reservation_id as rid
from payments p
where p.state = 'normal' and p.paid_type = 'coupon') as E on E.rid = B.rid
order by A.mid