Cumulative Car - Loui

set @startdate = '2019-04-01 00:00:00';
select
date_format(@startdate, '%Y-%m') as rmonth, cz.team as team, cz.region as region, cz.zid, cz.zname,
count(distinct cz.zid) as ending_zone, count(distinct cz.cid) as ending_car,
count(distinct cz.ncar) as cumulative_car,
ifnull(sum(rm.nuse),0) as rsv, ifnull(sum(dur),0) as dur, ifnull(sum(adj_dur),0) as adj_dur,
sum(rm.rental) as rental, sum(rm.discount) as discount,
sum(rm.rental) - sum(rm.discount) as nr
from
(select c.log_date as date1, z.city as team, z.region, z.id as zid, z.name as zname, c.car_id as cid, c.id as ncar
from car_zone_logs c, zones z
where c.zone_id=z.id
and date_sub(c.log_date, interval 1 day) >= @startdate
and date_sub(c.log_date, interval 1 day) < date_add(@startdate, interval 1 month)
and z.id not in (2,3,101,383,378,697,712,818)
group by date1, region, zid, cid)cz
left join
(select
date_format(r.return_at, '%Y-%m-%d') as date2,  r.start_zone_id as zid2, r.car_id as cid2, count(r.id) as nuse,
sum(timestampdiff(minute, r.start_at, r.return_at)/60) as dur,
sum((timestampdiff(minute, r.start_at, r.end_at)/60)/24)*12 + if(mod((timestampdiff(minute, r.start_at, r.end_at)/60), 24)>=12,12,mod((timestampdiff(minute, r.start_at, r.end_at)/60), 24)) as adj_dur,
sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent','oneway','d2d','mileage')),0)) as rental,
sum(ifnull((select sum(amount) from payments where state='normal' and reservation_id=r.id and paid_type ='coupon'),0)) as discount,
count(distinct m.id) as mau
from reservations r, members m
where r.member_id=m.id
and r.way in ('round','d2d','oneway')
and r.state = 'completed'
and m.imaginary in ('normal', 'sofam')
and r.return_at+interval 8 hour >= @startdate
and r.return_at+interval 8 hour < date_add(@startdate, interval 1 month)
and r.start_zone_id not in (2,3,101,383,378,697,712,818)
group by date2, zid2, cid2)rm

on (date_sub(cz.date1,interval 1 day)=rm.date2 and cz.cid=rm.cid2)
group by cz.zid;