F9-Monthly Index- Loui Help query - Daily Index Dashboard

##Without all factors###############################################################################################################################

set @startdate1 = '2019-09-01';
set @startdate2 = '2020-01-01';
use socar_malaysia;

Select A.month,
B.totalcars as ncars,
A.nuse,
A.nuse_rnd,
A.res_d2d,
A.res_oneway,
A.ncar_used,
A.nzone,
A.nmem,
A.rmil,
A.dur,
A.ad_sdur,
A.revenues,
A.coupons,
A.nr
from


(select concat(month(convert_tz(r.return_at, '+00:00', '+8:00')),"-","2019") as month,
month(convert_tz(r.return_at, '+00:00', '+8:00')) as month2,
(select count( czl.id) from car_zone_logs czl where month=month(czl.log_date+interval 8 hour) and czl.zone_state='normal') 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(if(r.way in('oneway','onewayReturn' ), r.id, null)) as res_oneway,
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','insurance')),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','insurance')),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) A

join
(select
 rmonth2 as month2, ncars as ncars, wtotal_ncars as totalcars

from (
  select rmonth2,
    (select count(cz.id) from car_zone_logs cz, zones z
      where cz.zone_id=z.id and z.id not in (2,3,101,383,378,712,818,786) and date_format(date_sub(cz.log_date, interval 1 day)+interval 8 hour, "%Y-%m-%d")=mlast
    ) as ncars,
    (select count(cz.id) from car_zone_logs cz, zones z
      where cz.zone_id=z.id and z.id not in (2,3,101,383,378,712,818,786) and date_sub(cz.log_date, interval 1 day)+interval 8 hour >= mfirst and date_sub(cz.log_date, interval 1 day)+interval 8 hour < mlast+interval 1 day
    ) as wtotal_ncars

  from (

    select
     rmonth2, min(date_format(rret,"%Y-%m-%d")) as mfirst, max(date_format(rret,"%Y-%m-%d")) as mlast

    from (
      select
        month(r.return_at+interval 8 hour) as rmonth2,
        r.start_at+interval 8 hour as rst, r.end_at+interval 8 hour as rend, r.return_at+interval 8 hour as rret
             from reservations r, members m, zones z
      where
        r.state='completed'
        and r.member_id=m.id
        and m.imaginary in ('normal', 'sofam')
        and r.start_zone_id=z.id
        and r.start_zone_id not in (2,3,101,383,378,697,712,818,786)
        and r.return_at + interval 8 hour >= @startdate1
        and r.return_at + interval 8 hour <= @startdate2
    ) temp
    group by rmonth2
  ) temp
  group by rmonth2
) temp
group by rmonth2) B

on A.month2 = B.month2

order by A.month2 desc;


##With all factors###############################################################################################################################

set @startdate1 = '2019-09-01';
set @startdate2 = '2020-01-01';
use socar_malaysia;

Select A.month,
B.totalcars as ncars,
A.nuse,
A.nuse_rnd,
A.res_d2d,
A.res_oneway,
A.ncar_used,
A.nzone,
A.nmem,
A.rmil,
A.dur,
A.ad_sdur,
A.revenues,
A.coupons,
A.nr
from


(select concat(month(convert_tz(r.return_at, '+00:00', '+8:00')),"-","2019") as month,
month(convert_tz(r.return_at, '+00:00', '+8:00')) as month2,
(select count( czl.id) from car_zone_logs czl where month=month(czl.log_date+interval 8 hour) and czl.zone_state='normal') 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(if(r.way in('oneway','onewayReturn' ), r.id, null)) as res_oneway,
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','insurance','penalty','fine','reservationFee','cancelFee','parkingFee','etc','accident')),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','insurance','penalty','fine','reservationFee','cancelFee','parkingFee','etc','accident')),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) A

join
(select
 rmonth2 as month2, ncars as ncars, wtotal_ncars as totalcars

from (
  select rmonth2,
    (select count(cz.id) from car_zone_logs cz, zones z
      where cz.zone_id=z.id and z.id not in (2,3,101,383,378,712,818,786) and date_format(date_sub(cz.log_date, interval 1 day)+interval 8 hour, "%Y-%m-%d")=mlast
    ) as ncars,
    (select count(cz.id) from car_zone_logs cz, zones z
      where cz.zone_id=z.id and z.id not in (2,3,101,383,378,712,818,786) and date_sub(cz.log_date, interval 1 day)+interval 8 hour >= mfirst and date_sub(cz.log_date, interval 1 day)+interval 8 hour < mlast+interval 1 day
    ) as wtotal_ncars

  from (

    select
     rmonth2, min(date_format(rret,"%Y-%m-%d")) as mfirst, max(date_format(rret,"%Y-%m-%d")) as mlast

    from (
      select
        month(r.return_at+interval 8 hour) as rmonth2,
        r.start_at+interval 8 hour as rst, r.end_at+interval 8 hour as rend, r.return_at+interval 8 hour as rret
             from reservations r, members m, zones z
      where
        r.state='completed'
        and r.member_id=m.id
        and m.imaginary in ('normal', 'sofam')
        and r.start_zone_id=z.id
        and r.start_zone_id not in (2,3,101,383,378,697,712,818,786)
        and r.return_at + interval 8 hour >= @startdate1
        and r.return_at + interval 8 hour <= @startdate2
    ) temp
    group by rmonth2
  ) temp
  group by rmonth2
) temp
group by rmonth2) B

on A.month2 = B.month2

order by A.month2 desc ;

##Without all factors###############################################################################################################################

set @startdate1 = '2019-09-01';
set @startdate2 = '2020-01-01';
use socar_malaysia;

Select A.month,
B.totalcars as ncars,
A.nuse,
A.nuse_rnd,
A.res_d2d,
A.res_oneway,
A.ncar_used,
A.nzone,
A.nmem,
A.rmil,
A.dur,
A.ad_sdur,
A.revenues,
A.coupons,
A.nr
from


(select concat(month(convert_tz(r.return_at, '+00:00', '+8:00')),"-","2019") as month,
month(convert_tz(r.return_at, '+00:00', '+8:00')) as month2,
(select count( czl.id) from car_zone_logs czl where month=month(czl.log_date+interval 8 hour) and czl.zone_state='normal') 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(if(r.way in('oneway','onewayReturn' ), r.id, null)) as res_oneway,
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','insurance')),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','insurance')),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) A

join
(select
 rmonth2 as month2, ncars as ncars, wtotal_ncars as totalcars

from (
  select rmonth2,
    (select count(cz.id) from car_zone_logs cz, zones z
      where cz.zone_id=z.id and z.id not in (2,3,101,383,378,712,818,786) and date_format(date_sub(cz.log_date, interval 1 day)+interval 8 hour, "%Y-%m-%d")=mlast
    ) as ncars,
    (select count(cz.id) from car_zone_logs cz, zones z
      where cz.zone_id=z.id and z.id not in (2,3,101,383,378,712,818,786) and date_sub(cz.log_date, interval 1 day)+interval 8 hour >= mfirst and date_sub(cz.log_date, interval 1 day)+interval 8 hour < mlast+interval 1 day
    ) as wtotal_ncars

  from (

    select
     rmonth2, min(date_format(rret,"%Y-%m-%d")) as mfirst, max(date_format(rret,"%Y-%m-%d")) as mlast

    from (
      select
        month(r.return_at+interval 8 hour) as rmonth2,
        r.start_at+interval 8 hour as rst, r.end_at+interval 8 hour as rend, r.return_at+interval 8 hour as rret
             from reservations r, members m, zones z
      where
        r.state='completed'
        and r.member_id=m.id
        and m.imaginary in ('normal', 'sofam')
        and r.start_zone_id=z.id
        and r.start_zone_id not in (2,3,101,383,378,697,712,818,786)
        and r.return_at + interval 8 hour >= @startdate1
        and r.return_at + interval 8 hour <= @startdate2
    ) temp
    group by rmonth2
  ) temp
  group by rmonth2
) temp
group by rmonth2) B

on A.month2 = B.month2

order by A.month2 desc