ahmedrahil786

NRPCPD and Zone PRPD

Dec 11th, 2019
256
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. #############Zone wise NRPCPD
  2.  
  3.  
  4. set @startdate1 = '2019-11-01';
  5. set @startdate2 = '2019-11-30';
  6. use socar_malaysia;
  7. 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
  8. (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,
  9. IFNULL(Sum(E.charges),0) as gross_rev,
  10. IFNULL(sum(F.coupon_Spent),0) as Coupon_Spent,
  11. round((IFNULL(sum(E.charges),0) - IFNULL(sum(F.coupon_Spent),0)),2) as net_rev from
  12. (select
  13. 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,
  14. IFNULL(sum(ra.mileage),0) as distance,
  15. round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,
  16. 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,
  17. Date(r.return_at + interval '8' hour) as return_Date,
  18. month(r.return_at + interval '8' hour) as month
  19. from reservations r left join members m on r.member_id = m.id
  20. left join reservation_appendixes ra on ra.reservation_id = r.id
  21. left join cars cr on cr.id = r.car_id
  22. left join car_classes cc on cc.id = cr.car_class_id
  23. left join zones z on z.id = r.start_zone_id
  24. where r.state in ('completed')
  25. and r.return_at + interval 8 hour >= @startdate1
  26. and r.return_at + interval 8 hour <= @startdate2
  27. and m.imaginary in ('sofam', 'normal')
  28. and r.member_id not in ('125', '127')
  29. and (YEAR(r.start_at) - YEAR(m.birthday)) < 100
  30. group by rid2) A
  31. left join
  32. (select c.reservation_id as rid, sum(c.amount) as charges from charges c
  33. where c.state='normal' and c.kind in ('rent','oneway','d2d','mileage','insurance')
  34. group by rid) E on A.rid2 = E.rid
  35. left join
  36. (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
  37. where p.state = 'normal' and p.paid_type = 'coupon'
  38. group by p.reservation_id) F on A.rid2 = F.rid
  39. group by 1,2 ) I
  40. left join
  41. (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
  42. left join car_classes cc on cc.id = cr.car_class_id
  43. left join zones z on z.id = czl.zone_id
  44. where czl.zone_state='normal'
  45. and czl.log_date + interval 8 hour >= @startdate1
  46. and czl.log_date + interval 8 hour <= @startdate2
  47. group by 1,2
  48. order by 1,2 desc) Q
  49. on Q.month = I.month and Q.zid = I.zid
  50. where I.month < '12'
  51. group by 1,2
  52.  
  53.  
  54. ################## Car Model PCPD
  55.  
  56. set @startdate1 = '2019-11-01';
  57. set @startdate2 = '2019-11-30';
  58. use socar_malaysia;
  59. 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
  60. (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,
  61. IFNULL(Sum(E.charges),0) as gross_rev,
  62. IFNULL(sum(F.coupon_Spent),0) as Coupon_Spent,
  63. round((IFNULL(sum(E.charges),0) - IFNULL(sum(F.coupon_Spent),0)),2) as net_rev from
  64. (select
  65. distinct r.id as rid2, r.member_id as mid, r.way as way, cc.car_name as car_name,
  66. IFNULL(sum(ra.mileage),0) as distance,
  67. round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,
  68. 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,
  69. Date(r.return_at + interval '8' hour) as return_Date,
  70. month(r.return_at + interval '8' hour) as month
  71. from reservations r left join members m on r.member_id = m.id
  72. left join reservation_appendixes ra on ra.reservation_id = r.id
  73. left join cars cr on cr.id = r.car_id
  74. left join car_classes cc on cc.id = cr.car_class_id
  75. where r.state in ('completed')
  76. and r.return_at + interval 8 hour >= @startdate1
  77. and r.return_at + interval 8 hour <= @startdate2
  78. and m.imaginary in ('sofam', 'normal')
  79. and r.member_id not in ('125', '127')
  80. and (YEAR(r.start_at) - YEAR(m.birthday)) < 100
  81. group by rid2) A
  82. left join
  83. (select c.reservation_id as rid, sum(c.amount) as charges from charges c
  84. where c.state='normal' and c.kind in ('rent','oneway','d2d','mileage','insurance')
  85. group by rid) E on A.rid2 = E.rid
  86. left join
  87. (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
  88. where p.state = 'normal' and p.paid_type = 'coupon'
  89. group by p.reservation_id) F on A.rid2 = F.rid
  90. group by 1,2 ) I
  91. left join
  92. (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
  93. left join car_classes cc on cc.id = cr.car_class_id where czl.zone_state='normal'
  94. and czl.log_date + interval 8 hour >= @startdate1
  95. and czl.log_date + interval 8 hour <= @startdate2
  96. group by 1,2
  97. order by 1,2 desc) Q
  98. on Q.month = I.month and Q.car_name = I.car_name
  99. where I.month < '12'
  100. group by 1,2
RAW Paste Data