Rahil-Mineng Query 2- Half

#reservation return at###############################################################################################################################
use socar_malaysia;

select
distinct z.id as zoneid,
z.region as region,
z.name as zonename,
datediff(now(),z.created_at) as daysdeployed,
#date_format(convert_tz(r.return_at, '+00:00', '+8:00'),'%Y-%m-%d') as date,
(select count(czl.id) from car_zone_logs czl where czl.zone_state='normal'and czl.zone_id = zoneid and date_format(convert_tz(r.return_at, '+00:00', '+8:00'),'%Y-%m-%d')=date_format(czl.log_date+interval 8 hour, "%Y-%m-%d")) as ncars,
count(r.id) as nuse,
count(if(r.way='round', r.id, null)) as nuse_rnd,
count(if(r.way='d2d', r.id, null)) as res_d2d,
count(distinct r.car_id) as ncar_used,
count(distinct z.id) as nzone,
count(distinct m.id) as nmem,
#count(if(r.way='oneway', r.id, null)) as res_ow,
sum(ra.mileage) as rmil,
sum(timestampdiff(minute, r.start_at, r.end_at)/60) as dur,
#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))) 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')),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')),0)
- ifnull((select sum(amount) from payments where state='normal' and reservation_id=r.id and paid_type ='coupon'),0)),2) as nr


from zones z, reservations r, 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')
    and r.return_at + interval 8 hour >= '2019-01-01'
    and r.return_at + interval 8 hour <= '2019-01-03'

group by z.id
order by z.id desc
;