Mineng Query - Biz Index Dashboard - daily

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

select 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 date=date_format(czl.log_date+interval 8 hour, "%Y-%m-%d") and czl.zone_state='normal') as ncars,
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,
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,
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')
    and r.return_at + interval 8 hour >= '2019-01-01'

group by date
order by date desc
;