F7 Monthly - F1 - 2018 - Query

#reservation return at###############################################################################################################################

set @startdate1 = '2018-01-01';
set @startdate2 = '2018-12-31';
use socar_malaysia;

select concat(month(convert_tz(r.return_at, '+00:00', '+8:00')),"-","2019") as month,
(select count(distinct czl.car_id) from car_zone_logs czl where month=month(czl.log_date+interval 8 hour) and czl.zone_state='normal' and czl.log_date + interval 8 hour >= @startdate1 and czl.log_date + interval 8 hour <= @startdate2 ) 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,
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')),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 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')
    and r.return_at + interval 8 hour >= @startdate1
    and r.return_at + interval 8 hour <= @startdate2

group by month
order by month desc
;