Rahil - Combo all reservations - V2.0 - Includes Coupons

set @startdate1 = '2019-07-15';
set @startdate2 = '2019-08-01';
use socar_malaysia;
Select M.date, M.ncars,IFNULL(M.city,0) as city,IFNULL(M.zid,0) as zid,IFNULL(M.region,0) as region,IFNULL(L.resv,0) as resv, IFNULL(L.nmem,0) as nmem,
IFNULL(L.dur,0) as dur, IFNULL(L.Ad_sdur,0) as adj_dur,IFNULL(L.distance,0) as dist,IFNULL(L.coupon,0) as coupon, IFNULL(L.gross_rev,0) as gross_rev,
IFNULL(L.coupon_spent,0) as coupon_spent, IFNULL(L.Net_rev,0) as net_rev  from
(select
distinct czl.zone_id as zid, Date((czl.log_date)) as date, dayname((czl.log_date)) as weekday, count(distinct czl.car_id ) as ncars , z.city as city , z.region as region
from car_zone_logs czl join zones z on z.id = czl.zone_id
where czl.log_date + interval '8' hour >= @startdate1
and czl.car_state = 'normal'
and czl.zone_state = 'normal'
group by 1,2
order by 2 desc) M
left join
(select I.date, I.zid as zid,Count(I.mid) as nmem,Count(I.rid) as resv, Sum(I.dur) as dur, Sum(I.ad_sdur) as Ad_sdur,Sum(I.distance) as distance, count(if(I.coupon ='none', null, I.coupon)) as coupon, Round(Sum(I.gross_rev),2) as gross_rev, ROUND(Sum(I.coupon_spent),2) as coupon_spent , ROUND(Sum(I.net_rev),2) as Net_rev from
(select A.return_date as date, A.rid as rid, A.mid as mid,A.zid as zid, A.region as region, A.city as city, A.car_name as car_name, (A.coupon) as coupon, A.dur as dur, A.ad_sdur as ad_sdur, A.distance as distance,
A.nuse_rnd as nuse_rnd,A.res_d2d as res_d2d,A.res_oneway as res_oneway , A.resv_hour as res_hour, A.occupy_start_hour as start_hour, A.age as age, IFNULL(E.charges,0) as gross_rev,
IFNULL(F.coupon_Spent,0) as Coupon_Spent,
round((IFNULL(E.charges,0) - IFNULL(F.coupon_Spent,0)),2) as net_rev from
(select
distinct r.id as rid, r.member_id as mid, z.id as zid, z.region, z.city, cc.car_name,IFNULL(co.comment,"none") as coupon,
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,
IFNULL(sum(ra.mileage),0) as distance,
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='oneway', r.id, null)) as res_oneway,
Date(r.return_at + interval '8' hour) as return_Date,
hour(r.start_at + interval '8' hour) as resv_hour,
hour(r.occupy_start_at + interval '8' hour) as occupy_start_hour,
(YEAR(r.start_at) - YEAR(m.birthday)) as age
from reservations r left join members m on r.member_id = m.id
left join zones z on z.id = r.start_zone_id
left join cars cr on cr.id = r.car_id
left join car_classes cc on cc.id = cr.car_class_id
left join coupons co on co.reservation_id = r.id
left join reservation_appendixes ra on ra.reservation_id = r.id
where r.state in ('completed')
and r.return_at + interval 8 hour >= @startdate1
and r.return_at + interval 8 hour <= @startdate2
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
and (YEAR(r.start_at) - YEAR(m.birthday)) < 100
group by rid) A
left join
(select c.reservation_id as rid, sum(c.amount) as charges from charges c
where c.state='normal' and c.kind in ('rent','oneway','d2d','mileage')
group by rid) E on A.rid = E.rid
left join
(select p.reservation_id as rid, month(p.created_at + interval '8' hour) as month,Year(p.created_at + interval '8' hour) as year, IFNULL(p.amount,0) as coupon_Spent from payments p
where p.state = 'normal' and p.paid_type = 'coupon'
group by p.reservation_id) F on A.rid = F.rid) I
where I.city not like ("-")
group by I.date,I.zid
order by date desc) L
on L.zid = M.zid and L.date = M.date
#Where L.date is not null
group by M.zid,M.date
order by M.date desc