F6 - Last 4 weeks

set @startdate = date_sub(CURDATE(), interval 1 day);
select
cz.operating,
cz.zid,
cz.zname,
cz.carclass,
cz.carname,
B.daysdeployed,
count(distinct cz.ncar) as ncar,
round(count(distinct cz.ncar)/B.daysdeployed,2) as davg,
IFNULL(sum(rm.nuse),0) as nuse,
IFNULL(sum(rm.nuse_rnd),0) as nuse_rnd,
IFNULL(sum(rm.res_d2d),0) as res_d2d,
IFNULL(sum(rm.ncar_used),0) as ncar_used,
count(distinct cz.zid) as nzone,
IFNULL(sum(rm.nmem),0) as nmem,
IFNULL(sum(rm.rmil),0) as rmil,
round(ifnull(sum(dur),0),2) as dur,
round(IFNULL(sum(rm.rental),0),2) as rental, round(IFNULL(sum(rm.discount),0),2) as discount,
round(IFNULL(sum(rm.rental) - sum(rm.discount),0),2) as nr,
round(IFNULL(sum(rm.nuse_rnd)/count(distinct cz.ncar),0),2) as nuse_pct


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, cc.id as carclass,
cc.car_name as carname,c.id as ncar,ca.id as carsid, count(if(z.state='Normal', z.id, null)) as operating
from car_zone_logs c, zones z , car_classes cc , cars ca
where c.zone_id=z.id
and c.car_id = ca.id
and ca.car_class_id = cc.id
and date_sub(c.log_date, interval 1 day) >= date_sub(@startdate, interval 4 week)
and date_sub(c.log_date, interval 1 day) < @startdate
and z.id not in (2,3,101,383,378,697,712,818)
group by date1, region, zid, cid,carname)cz

left join
(select count(distinct c.log_date) as daysdeployed, c.car_id as carid,c.zone_id as zid from car_zone_logs c
where date_sub(c.log_date, interval 1 day) >= date_sub(@startdate, interval 4 week)
and date_sub(c.log_date, interval 1 day) < @startdate
group by c.zone_id) B
on cz.zid = B.zid

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,
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,cc.id as carclass,
cc.car_name as carname,ca.id as carsid,
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','insurance')),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 , reservation_appendixes ra , zones z , car_classes cc , cars ca
where r.member_id=m.id
and r.start_zone_id = z.id
and r.id = ra.reservation_id
and r.car_id = ca.id
and ca.car_class_id = cc.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 >= date_sub(@startdate, interval 4 week)
and r.return_at+interval 8 hour < @startdate
and r.start_zone_id not in (2,3,101,383,378,697,712,818)
group by date2, zid2, cid2, carname)rm

on (date_sub(cz.date1,interval 1 day)=rm.date2 and cz.cid=rm.cid2 and cz.zid = rm.zid2 and cz.carsid = rm.carsid)

left join

(select uu.*, ifnull(cc.mft,0) as mft

from(
  select z.zone_id as zid

  from car_zone_logs z

  where
  date_sub(z.log_date, interval 1 day) >= @startdate-interval 4 week
  and date_sub(z.log_date, interval 1 day) < @startdate
  and z.zone_id not in (2,3,101,383,378,697,712,818,786)

  group by zid
  order by zid desc

) uu
left join
(select zid2, count(distinct if(before_use=0, mid, null)) as mft
  from
  (select
      z.id as zid2,  r.member_id as mid,
      (select count(id)
      from reservations
      where member_id=r.member_id
      and state='completed'
      and return_at+interval 8 hour < @startdate
      and start_zone_id not in (2,3,101,383,378,697,712,818,786)) as before_use
    from reservations r, members m, zones z
    where
      r.state='completed'
      and r.member_id=m.id
      and r.start_zone_id = z.id
      and m.imaginary in ('normal', 'sofam')
      and r.return_at+interval 8 hour >= @startdate-interval 4 week
      and r.return_at+interval 8 hour < @startdate
      and r.start_zone_id not in (2,3,101,383,378,697,712,818,786)

  group by r.member_id
  ) temp

  where
    before_use=0

group by zid2) cc
on (uu.zid = cc.zid2)

group by zid
order by zid) A

on A.zid = cz.zid
group by cz.zid,cz.carname,cz.carsid;