Stephy - Members last 30 Days , BMW Mini

set @startdate1 = '2017-01-01';
set @startdate2 = '2019-08-01';
use socar_malaysia;
Select distinct Q.mid,Q.name,Q.email from
(select A.return_date as date, A.rid as rid, A.mid as mid,A.name as name, A.email as email, A.car_name as car_name,IFNULL(E.charges,0) as gross_rev from
(select
distinct r.id as rid, r.member_id as mid, cc.car_name,Date(r.return_at + interval '8' hour) as return_Date, m.display_name as name, m.email as email
from reservations r left join members m on r.member_id = m.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 in ('completed')
and r.return_at + interval 8 hour >= @startdate1
and r.return_at + interval 8 hour <= @startdate2
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
and (YEAR(r.start_at) - YEAR(m.birthday)) < 100
and m.email not like "%@socar.my"
group by rid) 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')
group by rid) E on A.rid = E.rid
 ) Q
where Q.gross_rev > 0
and Q.car_name Not in ('330e','MINI 3door','MINI 5door','MINI S 3door','MINI S JCW 5door','CX-5','BR-V','HR-V')