One way Zones Duration vs Non One way Zones Duration - Rahil

################ One way Zones PC PD

set @startdate1 = '2019-01-01';
set @startdate2 = '2019-09-01';
Select B.date, sum(C.resv) as resv, Sum(B.ncars) as oneway_zone_cars , Sum(C.dur) as duration, Sum(C.ad_sdur) as ad_sdur, Sum(C.Gross_rev) as Gross_rev, sum(Coupon_spent) as Coupon_spent, sum(Net_rev) as Net_rev ,
Round((sum(C.resv)/Sum(B.ncars)),2) as RESV_PC_PD, Round((Sum(C.dur)/Sum(B.ncars)),2) as DUR_PC_PD, Round((sum(Net_rev)/Sum(B.ncars)),2) as NR_PC_PD from
(select distinct r.start_zone_id as zid
from reservations r
where r.way in ('oneway', 'onewayReturn')
and r.start_at + interval 8 hour > '2019-06-01') A
join
(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) B
on A.zid = B.zid
left join
(Select I.date as date, count(I.rid) as resv, I.zid as zid, sum(I.dur) as dur, sum(I.ad_sdur) as ad_sdur, sum(I.gross_rev) as Gross_rev, sum(I.coupon_spent) as Coupon_spent, Sum(I.net_rev) as Net_rev from
(select A.rid,A.return_date as date, A.zid, A.dur as dur, A.ad_sdur as ad_sdur, 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.start_zone_id as zid,
Date(r.return_at + interval '8' hour) as return_Date,
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
from reservations r left join members m on r.member_id = m.id
left join zones z on z.id = r.start_zone_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
Group by I.date,I.zid) C
on C.date = B.date and C.zid = A.zid
Group by B.date
order by 1 Desc ;

################ Non- One way Zones PC PD


set @startdate1 = '2019-01-01';
set @startdate2 = '2019-09-01';
Select B.date, sum(C.resv) as resv, Sum(B.ncars) as oneway_zone_cars , Sum(C.dur) as duration, Sum(C.ad_sdur) as ad_sdur, Sum(C.Gross_rev) as Gross_rev, sum(Coupon_spent) as Coupon_spent, sum(Net_rev) as Net_rev,
Round((sum(C.resv)/Sum(B.ncars)),2) as RESV_PC_PD, Round((Sum(C.dur)/Sum(B.ncars)),2) as DUR_PC_PD, Round((sum(Net_rev)/Sum(B.ncars)),2) as NR_PC_PD from
(select P.zid as zid from
(select distinct r.start_zone_id as zid
from reservations r
where r.start_zone_id not in (2,3,101)) P
left join
(select distinct r.start_zone_id as zid
from reservations r
where r.way in ('oneway', 'onewayReturn')
and r.start_at + interval 8 hour > '2019-06-01'
group by 1
order by 1 asc) Q
on P.zid = Q.zid
where Q.zid is null) A
join
(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) B
on A.zid = B.zid
left join
(Select I.date as date, count(I.rid) as resv, I.zid as zid, sum(I.dur) as dur, sum(I.ad_sdur) as ad_sdur, sum(I.gross_rev) as Gross_rev, sum(I.coupon_spent) as Coupon_spent, Sum(I.net_rev) as Net_rev from
(select A.rid,A.return_date as date, A.zid, A.dur as dur, A.ad_sdur as ad_sdur, 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.start_zone_id as zid,
Date(r.return_at + interval '8' hour) as return_Date,
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
from reservations r left join members m on r.member_id = m.id
left join zones z on z.id = r.start_zone_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
Group by I.date,I.zid) C
on C.date = B.date and C.zid = A.zid
Group by B.date
order by 1 Desc