API tools faq
paste
ahmedrahil786

4 queries - D2D Vs Night time Vs One way Vs Round - Amri

ahmedrahil786
Oct 2nd, 2019
175
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.51 KB | None | 0 0
raw download clone embed print report
  1. #### Change to to Month to latest month
  2.  
  3. set @startdate1 = '2019-10-01';
  4. set @startdate2 = '2019-11-01';
  5. use socar_malaysia;
  6. select A.return_date as date, A.rid2 as rid, A.dur as dur, A.ad_sdur as ad_sdur, A.way as way,
  7. IFNULL(E.charges,0) as gross_rev,
  8. IFNULL(F.coupon_Spent,0) as Coupon_Spent,
  9. round((IFNULL(E.charges,0) - IFNULL(F.coupon_Spent,0)),2) as net_rev from
  10. (select
  11. distinct r.id as rid2, r.member_id as mid, r.way as way,
  12. IFNULL(sum(ra.mileage),0) as distance,
  13. round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,
  14. 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,
  15. Date(r.return_at + interval '8' hour) as return_Date,
  16. ifnull((select min(id) from reservations where state='completed' and r.member_id=member_id group by member_id),0) as firstRes
  17. from reservations r left join members m on r.member_id = m.id
  18. left join reservation_appendixes ra on ra.reservation_id = r.id
  19. where r.state in ('completed')
  20. and r.return_at + interval 8 hour >= @startdate1
  21. and r.return_at + interval 8 hour <= @startdate2
  22. and m.imaginary in ('sofam', 'normal')
  23. and r.member_id not in ('125', '127')
  24. and (YEAR(r.start_at) - YEAR(m.birthday)) < 100
  25. group by rid2) A
  26. left join
  27. (select c.reservation_id as rid, sum(c.amount) as charges from charges c
  28. where c.state='normal' and c.kind in ('rent','oneway','d2d','mileage')
  29. group by rid) E on A.rid2 = E.rid
  30. left join
  31. (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
  32. where p.state = 'normal' and p.paid_type = 'coupon'
  33. group by p.reservation_id) F on A.rid2 = F.rid
  34. ;
  35.  
  36. ##########################################################################################################################################################################################
  37.  
  38. set @startDate = '2019-01-01 00:00:00';
  39.  
  40. select
  41. WEEKofyear(convert_tz(r.start_at, '+00:00', '+8:00')) as start_week,
  42. DATE(convert_tz(r.start_at, '+00:00', '+8:00')) as start_date,
  43. czl.total_cars as total_cars,
  44. count(distinct r.car_id) as used_cars,
  45. count(r.id) as srid,
  46. count(distinct r.member_id) as dau,
  47. round(SUM(timestampdiff(minute, convert_tz(r.start_at, '+00:00', '+8:00'), convert_tz(r.end_at, '+00:00', '+8:00'))/60),2) as sdur,
  48. round(sum(floor(timestampdiff(minute, convert_tz(r.start_at, '+00:00', '+8:00'), convert_tz(r.end_at, '+00:00', '+8:00'))/60/24)*12+if(mod(timestampdiff(minute, convert_tz(r.start_at, '+00:00', '+8:00'), convert_tz(r.end_at, '+00:00', '+8:00'))/60,24)>=12,12,mod(timestampdiff(minute, convert_tz(r.start_at, '+00:00', '+8:00'), convert_tz(r.end_at, '+00:00', '+8:00'))/60,24))),2) as adur,
  49. round(SUM(c.rev),2) AS rev,
  50. round(SUM(c.rev - IFNULL(p.coupon,0)),2) AS net_rev
  51. from reservations r
  52. left join members on r.member_id = members.id
  53. left join coupons on r.id = coupons.reservation_id
  54. left join (select IFNULL(payments.amount,0) as coupon, payments.reservation_id as rid from payments where payments.state = 'normal' and payments.paid_type = 'coupon') as p on p.rid = r.id
  55. left join (select IFNULL(SUM(charges.amount),0) as rev, charges.reservation_id as rid from charges where charges.state = 'normal' and charges.kind IN ('rent' , 'oneway', 'd2d', 'mileage') group by charges.reservation_id) as c on r.id = c.rid
  56. left join (select log_date, COUNT(car_zone_logs.car_id) as total_cars from car_zone_logs where car_state = 'normal' and zone_state = 'normal' group by log_date) as czl on czl.log_date = DATE(convert_tz(r.start_at,'+00:00', '+8:00'))
  57. where r.state = 'completed' and members.imaginary in ('sofam', 'normal')
  58. and convert_tz(r.start_at, '+00:00', '+8:00') >= @startDate
  59. # Only Monday - Thursday
  60. and DAYNAME(convert_tz(r.start_at, '+00:00', '+8:00')) in ('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday', 'Sunday')
  61. # Bookings made between 9 PM to 8 AM
  62. and (TIME(convert_tz(r.created_at, '+00:00', '+8:00')) between '00:00:00' and '08:00:00' or TIME(convert_tz(r.created_at, '+00:00', '+8:00')) between '21:00:00' and '23:59:59')
  63. # Less than 12 hours
  64. and timestampdiff(minute, r.start_at, r.end_at)/60 < 12
  65. group by 2
  66. order by start_date desc
  67. ;
  68.  
  69. ##########################################################################################################################################################################################
  70.  
  71. #### Change to to Month to latest month
  72.  
  73. set @startdate1 = '2019-10-01';
  74. set @startdate2 = '2019-11-01';
  75. use socar_malaysia;
  76. select A.return_date as date, A.rid2 as rid, A.mid as mid, A.dur as dur, A.ad_sdur as ad_sdur, A.way as way,
  77. IFNULL(E.charges,0) as gross_rev,
  78. IFNULL(F.coupon_Spent,0) as Coupon_Spent,
  79. round((IFNULL(E.charges,0) - IFNULL(F.coupon_Spent,0)),2) as net_rev, if(rid2=firstRes,1,0) as mft from
  80. (select
  81. distinct r.id as rid2, r.member_id as mid, r.way as way,
  82. IFNULL(sum(ra.mileage),0) as distance,
  83. round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,
  84. 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,
  85. Date(r.return_at + interval '8' hour) as return_Date,
  86. ifnull((select min(id) from reservations where state='completed' and r.member_id=member_id group by member_id),0) as firstRes
  87. from reservations r left join members m on r.member_id = m.id
  88. left join reservation_appendixes ra on ra.reservation_id = r.id
  89. where r.state in ('completed')
  90. and r.return_at + interval 8 hour >= @startdate1
  91. and r.return_at + interval 8 hour <= @startdate2
  92. and m.imaginary in ('sofam', 'normal')
  93. and r.member_id not in ('125', '127')
  94. and (YEAR(r.start_at) - YEAR(m.birthday)) < 100
  95. group by rid2) A
  96. left join
  97. (select c.reservation_id as rid, sum(c.amount) as charges from charges c
  98. where c.state='normal' and c.kind in ('rent','oneway','d2d','mileage')
  99. group by rid) E on A.rid2 = E.rid
  100. left join
  101. (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
  102. where p.state = 'normal' and p.paid_type = 'coupon'
  103. group by p.reservation_id) F on A.rid2 = F.rid
  104.  
  105. ;
  106.  
  107. ##########################################################################################################################################################################################
  108.  
  109.  
  110. set @startDate = '2019-01-01 00:00:00';
  111.  
  112. select A.* , if(rid2=firstRes,1,0) as mft from
  113. (select
  114. DISTINCT r.id as rid2,
  115. r.member_id as mid,
  116. WEEKofyear(convert_tz(r.start_at, '+00:00', '+8:00')) as start_week,
  117. DATE(convert_tz(r.start_at, '+00:00', '+8:00')) as start_date,
  118. czl.total_cars as total_cars,
  119. count(distinct r.car_id) as used_cars,
  120. count(r.id) as srid,
  121. count(distinct r.member_id) as dau,
  122. ifnull((select min(id) from reservations where state='completed' and r.member_id=member_id group by member_id),0) as firstRes,
  123. round(SUM(timestampdiff(minute, convert_tz(r.start_at, '+00:00', '+8:00'), convert_tz(r.end_at, '+00:00', '+8:00'))/60),2) as sdur,
  124. round(sum(floor(timestampdiff(minute, convert_tz(r.start_at, '+00:00', '+8:00'), convert_tz(r.end_at, '+00:00', '+8:00'))/60/24)*12+if(mod(timestampdiff(minute, convert_tz(r.start_at, '+00:00', '+8:00'), convert_tz(r.end_at, '+00:00', '+8:00'))/60,24)>=12,12,mod(timestampdiff(minute, convert_tz(r.start_at, '+00:00', '+8:00'), convert_tz(r.end_at, '+00:00', '+8:00'))/60,24))),2) as adur,
  125. round(SUM(c.rev),2) AS rev,
  126. round(SUM(c.rev - IFNULL(p.coupon,0)),2) AS net_rev
  127. from reservations r
  128. left join members on r.member_id = members.id
  129. left join coupons on r.id = coupons.reservation_id
  130. left join (select IFNULL(payments.amount,0) as coupon, payments.reservation_id as rid from payments where payments.state = 'normal' and payments.paid_type = 'coupon') as p on p.rid = r.id
  131. left join (select IFNULL(SUM(charges.amount),0) as rev, charges.reservation_id as rid from charges where charges.state = 'normal' and charges.kind IN ('rent' , 'oneway', 'd2d', 'mileage') group by charges.reservation_id) as c on r.id = c.rid
  132. left join (select log_date, COUNT(car_zone_logs.car_id) as total_cars from car_zone_logs where car_state = 'normal' and zone_state = 'normal' group by log_date) as czl on czl.log_date = DATE(convert_tz(r.start_at,'+00:00', '+8:00'))
  133. where r.state = 'completed' and members.imaginary in ('sofam', 'normal')
  134. and convert_tz(r.start_at, '+00:00', '+8:00') >= @startDate
  135. # Only Monday - Thursday
  136. and DAYNAME(convert_tz(r.start_at, '+00:00', '+8:00')) in ('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday', 'Sunday')
  137. # Bookings made between 9 PM to 8 AM
  138. and (TIME(convert_tz(r.created_at, '+00:00', '+8:00')) between '00:00:00' and '08:00:00' or TIME(convert_tz(r.created_at, '+00:00', '+8:00')) between '21:00:00' and '23:59:59')
  139. # Less than 12 hours
  140. and timestampdiff(minute, r.start_at, r.end_at)/60 < 12
  141. group by 1
  142. order by start_date desc) A
  143.  
  144. ;
Advertisement
Add Comment
Please, Sign In to add comment
Public Pastes
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!