SHARE
TWEET

Superstars Trend - Date wise (Like and Or Command Used)

ahmedrahil786 Nov 13th, 2019 (edited) 124 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. set @startdate1 = date_sub(CURDATE(), interval 4 month);
  2. set @startdate2 = date_add(CURDATE(), interval 10 day);
  3. use socar_malaysia;
  4.  
  5. Select B.* from
  6.  
  7. (select
  8. distinct r.member_id as mid,IFNULL(co.comment,"") as coupon
  9. from reservations r left join members m on r.member_id = m.id
  10. left join coupons co on co.reservation_id = r.id
  11. where r.state in ('completed')
  12. and r.return_at + interval 8 hour >= @startdate1
  13. and r.return_at + interval 8 hour <= @startdate2
  14. and m.imaginary in ('sofam', 'normal')
  15. and r.member_id not in ('125', '127')
  16. and (YEAR(r.start_at) - YEAR(m.birthday)) < 100
  17. and (co.comment like ("%Superstar%") or co.comment like ("%Rockstar%"))
  18. group by 1) A
  19.  
  20. join
  21.  
  22. (select A.return_date as return_date,A.start_date as start_date, A.created_hour as created_hour, A.rid2 as rid, A.mid as mid, A.region as region, A.city as city, A.car_name as car_name, A.name as name,
  23. A.coupon as coupon, A.dur as dur, A.ad_sdur as ad_sdur, A.distance as distance,
  24. A.resv_hour as res_hour, A.occupy_start_hour as occupy_start_hour, A.age as age, IFNULL(E.charges,0) as gross_rev,
  25. IFNULL(F.coupon_Spent,0) as Coupon_Spent,
  26. round((IFNULL(E.charges,0) - IFNULL(F.coupon_Spent,0)),2) as net_rev from
  27. (select
  28. distinct r.id as rid2, r.member_id as mid, z.region, z.city, cc.car_name,IFNULL(co.comment,"") as coupon, m.display_name as name,
  29. round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,
  30. 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,
  31. IFNULL(sum(ra.mileage),0) as distance,
  32. Date(r.return_at + interval '8' hour) as return_Date,
  33. hour(r.created_at + interval '8' hour) as created_hour,
  34. Date(r.start_at + interval '8' hour) as start_Date,
  35. hour(r.start_at + interval '8' hour) as resv_hour,
  36. hour(r.occupy_start_at + interval '8' hour) as occupy_start_hour,
  37. (YEAR(r.start_at) - YEAR(m.birthday)) as age
  38. from reservations r left join members m on r.member_id = m.id
  39. left join zones z on z.id = r.start_zone_id
  40. left join cars cr on cr.id = r.car_id
  41. left join car_classes cc on cc.id = cr.car_class_id
  42. left join coupons co on co.reservation_id = r.id
  43. left join reservation_appendixes ra on ra.reservation_id = r.id
  44. where r.state in ('completed')
  45. and r.return_at + interval 8 hour >= @startdate1
  46. and r.return_at + interval 8 hour <= @startdate2
  47. and m.imaginary in ('sofam', 'normal')
  48. and r.member_id not in ('125', '127')
  49. and (YEAR(r.start_at) - YEAR(m.birthday)) < 100
  50. group by rid2) A
  51. left join
  52. (select c.reservation_id as rid, sum(c.amount) as charges from charges c
  53. where c.state='normal' and c.kind in ('rent','oneway','d2d','mileage')
  54. group by rid) E on A.rid2 = E.rid
  55. left join
  56. (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
  57. where p.state = 'normal' and p.paid_type = 'coupon'
  58. group by p.reservation_id) F on A.rid2 = F.rid) B
  59. on B.mid = A.mid
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