MFT date and Coupon used - MFTs - To check Spend - Rahil

Select A.mid, A.rid, A.date, A.dur, A.ad_sdur, A.coupon, A.car_name,
IFNULL(E.charges,0) as gross_rev, IFNULL(F.coupon_Spent,0) as Coupon_Spent,
round((IFNULL(E.charges,0) - IFNULL(F.coupon_Spent,0)),2) as net_rev from
(select m.id as mid,
min(r.id) as rid,
round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,IFNULL(co.comment,"") as coupon,cc.car_name,
round(sum(floor(timestampdiff(minute, r.start_at, r.end_at)/60/24)*12+if(mod(timestampdiff(hour, r.start_at, r.end_at),24)>=12,12,mod(timestampdiff(minute, r.start_at, r.end_at)/60,24))),2) as ad_sdur,
Date(r.start_at + interval 8 hour) as date
from members m
left join reservations r on r.member_id = m.id
left join coupons co on co.reservation_id = r.id
left join cars cr on cr.id = r.car_id
left join car_classes cc on cc.id = cr.car_class_id
where r.state='completed'
and m.imaginary in ('sofam', 'normal')
group by m.id) A
left join
(select c.reservation_id as rid, sum(c.amount) as charges from charges c
where c.state='normal' and c.kind in ('rent','oneway','d2d','mileage','insurance')
group by rid) E on A.rid = E.rid
left join
(select p.reservation_id as rid, month(p.created_at + interval '8' hour) as month,Year(p.created_at + interval '8' hour) as year, IFNULL(p.amount,0) as coupon_Spent from payments p
where p.state = 'normal' and p.paid_type = 'coupon'
group by p.reservation_id) F on A.rid = F.rid
order by 1 desc