Shiva Query - For reservation - R.red_at - for predictions

set @startDate = '2020-03-10 00:00:00';
select
    week(convert_tz(r.end_at, '+00:00', '+8:00'),3) as week,
    date_format(convert_tz(r.end_at, '+00:00', '+8:00'),'%Y-%m-%d') as date,
    (select count(czl.id) from car_zone_logs czl where date=date_format(czl.log_date, "%Y-%m-%d") and czl.zone_state='normal' group by date_format(czl.log_date, "%Y-%m-%d")) as ncars,
    (select count(distinct czl.zone_id) from car_zone_logs czl where date=date_format(czl.log_date+interval 8 hour, "%Y-%m-%d") and czl.zone_state='normal') as nzones,
    count(distinct r.car_id) as ncar_used,
    count(distinct z.id) as nzone,
    count(r.id) as res,
    count(if(r.way='d2d', r.id, null)) as res_d2d,
    count(if(r.way='oneway', r.id, null)) as res_ow,
    round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,
    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,
    round(sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent','oneway','d2d','mileage','insurance')),0)),2) as revenues,
    round(sum(ifnull((select sum(amount) from payments where state='normal' and reservation_id=r.id and paid_type ='coupon'),0)),2) as coupons,
    round(sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent','oneway','d2d','mileage','insurance')),0)
        - ifnull((select sum(amount) from payments where state='normal' and reservation_id=r.id and paid_type ='coupon'),0)),2) as nr,
    count(distinct m.id) as dau,
    sum(ra.mileage) as rmil
from reservations r, zones z, members m, reservation_appendixes ra
where
    r.start_zone_id = z.id
    and r.member_id = m.id
    and r.id = ra.reservation_id
    and m.imaginary in ('sofam', 'normal')
    and r.member_id not in ('125', '127')
    and z.id not in (2, 3, 101)
    and r.state in ('completed','inUse','reserved')
    and r.end_at+ interval 8 hour >= @startDate
    and r.end_at+ interval 8 hour <  date_add(@startDate, interval 2 week)
group by date
order by date desc