SHARE
TWEET

NRPCPD and Zone PRPD

ahmedrahil786 Dec 11th, 2019 (edited) 116 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
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. OK, I Understand
 
Top