**Not a member of Pastebin yet?**

**, it unlocks many cool features!**

__Sign Up__- #############Zone wise NRPCPD
- set @startdate1 = '2019-11-01';
- set @startdate2 = '2019-11-30';
- use socar_malaysia;
- Select I.month as month, I.zone_name as zone_name, I.region as region, I.city as city, Round(I.net_rev,2) as Net_rev, ROUND(I.Total_reservations/Q.cars,2) as RESPCPD, ROUND(I.net_rev/Q.cars,2) as RevPCPD, Round(I.Dur/Q.cars,2) as DURPCPD from
- (select Month(A.return_date) as month, A.name as zone_name, A.region as region, A.city as city, A.zid as zid, count(A.rid2) as Total_reservations, count(distinct A.mid) as Total_Unique_Members, sum(A.distance) as distance, sum(A.dur) as dur, sum(A.ad_sdur) as ad_sdur,
- IFNULL(Sum(E.charges),0) as gross_rev,
- IFNULL(sum(F.coupon_Spent),0) as Coupon_Spent,
- round((IFNULL(sum(E.charges),0) - IFNULL(sum(F.coupon_Spent),0)),2) as net_rev from
- (select
- distinct r.id as rid2, r.member_id as mid, r.way as way, cc.car_name as car_name, r.start_zone_id as zid, z.name as name, z.region as region, z.city as city,
- IFNULL(sum(ra.mileage),0) as distance,
- 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,
- Date(r.return_at + interval '8' hour) as return_Date,
- month(r.return_at + interval '8' hour) as month
- from reservations r left join members m on r.member_id = m.id
- left join reservation_appendixes ra on ra.reservation_id = r.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 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 rid2) 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','insurance')
- group by rid) E on A.rid2 = 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.rid2 = F.rid
- group by 1,2 ) I
- left join
- (select month(czl.log_date + interval 8 hour ) as month, czl.zone_id as zid, z.name as zone_name, count(czl.id) as cars from car_zone_logs czl left join cars cr on cr.id = czl.car_id
- left join car_classes cc on cc.id = cr.car_class_id
- left join zones z on z.id = czl.zone_id
- where czl.zone_state='normal'
- and czl.log_date + interval 8 hour >= @startdate1
- and czl.log_date + interval 8 hour <= @startdate2
- group by 1,2
- order by 1,2 desc) Q
- on Q.month = I.month and Q.zid = I.zid
- where I.month < '12'
- group by 1,2
- ################## Car Model PCPD
- set @startdate1 = '2019-11-01';
- set @startdate2 = '2019-11-30';
- use socar_malaysia;
- Select I.month as month, I.car_name as car_name, Round(I.net_rev,2) as Net_rev, ROUND(I.Total_reservations/Q.cars,2) as RESPCPD, ROUND(I.net_rev/Q.cars,2) as RevPCPD, Round(I.Dur/Q.cars,2) as DURPCPD from
- (select Month(A.return_date) as month, A.car_name as car_name, count(A.rid2) as Total_reservations, count(distinct A.mid) as Total_Unique_Members, sum(A.distance) as distance, sum(A.dur) as dur, sum(A.ad_sdur) as ad_sdur,
- IFNULL(Sum(E.charges),0) as gross_rev,
- IFNULL(sum(F.coupon_Spent),0) as Coupon_Spent,
- round((IFNULL(sum(E.charges),0) - IFNULL(sum(F.coupon_Spent),0)),2) as net_rev from
- (select
- distinct r.id as rid2, r.member_id as mid, r.way as way, cc.car_name as car_name,
- IFNULL(sum(ra.mileage),0) as distance,
- 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,
- Date(r.return_at + interval '8' hour) as return_Date,
- month(r.return_at + interval '8' hour) as month
- from reservations r left join members m on r.member_id = m.id
- left join reservation_appendixes ra on ra.reservation_id = r.id
- left join cars cr on cr.id = r.car_id
- left join car_classes cc on cc.id = cr.car_class_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 rid2) 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','insurance')
- group by rid) E on A.rid2 = 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.rid2 = F.rid
- group by 1,2 ) I
- left join
- (select month(czl.log_date + interval 8 hour ) as month, cc.car_name as car_name, count(czl.id) as cars from car_zone_logs czl left join cars cr on cr.id = czl.car_id
- left join car_classes cc on cc.id = cr.car_class_id where czl.zone_state='normal'
- and czl.log_date + interval 8 hour >= @startdate1
- and czl.log_date + interval 8 hour <= @startdate2
- group by 1,2
- order by 1,2 desc) Q
- on Q.month = I.month and Q.car_name = I.car_name
- where I.month < '12'
- group by 1,2

RAW Paste Data

We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.