MFT by Region - Leon Dashboard

set @startdate = date_sub(CURDATE(), interval 1 day);
set @startdate2 = date_sub(@startdate, interval 1 month);

select
    members.id as mid,
    MIN(reservations.id) as first_res,
    date(reservations.occupy_start_at + interval '8' hour) as date,
    (YEAR(reservations.occupy_start_at) - YEAR(members.birthday)) as age,
    COUNT(reservations.id) as srid,
    round((timestampdiff(minute, convert_tz(reservations.start_at, '+00:00', '+8:00'), convert_tz(reservations.end_at, '+00:00', '+8:00'))/60),2) as sdur,
    zones.city as zone_city,
    zones.region as zone_region,
    cc.car_name as carname,
    #cars.id as carid,
    #IFNULL(ma.domain_locality,0) as location,
    IFNULL(ma.recommender,"") as referral,
    IFNULL(coupons.comment,"") as coupon,
    IFNULL(round(c.rev,2),0) as rev,
    round((IFNULL(c.rev,0) - IFNULL(p.coupon,0)),2) as net_rev,
    round(IFNULL((IFNULL(p.coupon,0)/c.rev),0),2) as coupon_rate
from reservations
left join members on reservations.member_id = members.id
left join coupons on coupons.reservation_id = reservations.id
left join zones on reservations.start_zone_id = zones.id
left join (
        select IFNULL(payments.amount,0) as coupon, payments.reservation_id as rid
        from payments
        where payments.state = 'normal' and payments.paid_type = 'coupon'
    ) as p on p.rid = reservations.id
left join (
        select sum(charges.amount) as rev, charges.reservation_id as rid
        from charges
        where charges.state = 'normal' and charges.kind IN ('rent' , 'oneway', 'd2d', 'mileage')
        group by charges.reservation_id
   ) as c ON reservations.id = c.rid
left join cars on cars.id = reservations.car_id
left join car_classes cc on cc.id = cars.car_class_id
left join member_appendixes ma on members.id = ma.member_id
where
reservations.state = 'completed'
and members.imaginary in ('normal', 'sofam')
and DATE(CONVERT_TZ(members.created_at,'+00:00','+8:00')) <= @startDate
and DATE(CONVERT_TZ(members.created_at,'+00:00','+8:00')) >= @startDate2
group by members.id