SHARE
TWEET

Dashboard for Leon - NRPCPD, MFT and MAU / Region

ahmedrahil786 May 21st, 2019 73 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. set @startdate = date_sub(CURDATE(), interval 1 day);
  2. set @startdate2 = date_sub(@startdate, interval 1 month);
  3. select
  4. date_format(@startdate, '%Y-%m') as rmonth, cz.team as team, cz.region as region,
  5. count(distinct cz.zid) as ending_zone, count(distinct cz.cid) as ending_car,
  6. count(distinct cz.ncar) as cumulative_car,
  7. ifnull(sum(rm.nuse),0) as rsv, round(ifnull(sum(dur),0),2) as dur, round(ifnull(sum(adj_dur),0),2) as adj_dur,
  8. round(sum(rm.rental),2) as rental, round(sum(rm.discount),2) as discount,
  9. round(sum(rm.rental) - sum(rm.discount),2) as nr,
  10. B.mau,
  11. A.mft
  12. from
  13. (select c.log_date as date1, z.city as team, z.region as region, z.id as zid, z.name as zname, c.car_id as cid, c.id as ncar
  14. from car_zone_logs c, zones z
  15. where c.zone_id=z.id
  16. and date_sub(c.log_date, interval 1 day) <= @startdate
  17. and date_sub(c.log_date, interval 1 day) >= @startdate2
  18. and z.id not in (2,3,101,383,378,697,712,818)
  19. group by date1, region, zid, cid)cz
  20. left join
  21. (select
  22. date_format(r.return_at, '%Y-%m-%d') as date2,  r.start_zone_id as zid2, r.car_id as cid2, count(r.id) as nuse, z.region,
  23. sum(timestampdiff(minute, r.start_at, r.return_at)/60) as dur,
  24. sum((timestampdiff(minute, r.start_at, r.end_at)/60)/24)*12 + if(mod((timestampdiff(minute, r.start_at, r.end_at)/60), 24)>=12,12,mod((timestampdiff(minute, r.start_at, r.end_at)/60), 24)) as adj_dur,
  25. sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent','oneway','d2d','mileage')),0)) as rental,
  26. sum(ifnull((select sum(amount) from payments where state='normal' and reservation_id=r.id and paid_type ='coupon'),0)) as discount
  27. #count(distinct m.id) as mau
  28. from reservations r, members m , zones z
  29. where r.member_id=m.id
  30. and r.start_zone_id = z.id
  31. and r.way in ('round','d2d','oneway')
  32. and r.state = 'completed'
  33. and m.imaginary in ('normal', 'sofam')
  34. and r.return_at+interval 8 hour <= @startdate
  35. and r.return_at+interval 8 hour >= @startdate2
  36. and r.start_zone_id not in (2,3,101,383,378,697,712,818)
  37. group by date2, zid2, cid2)rm
  38.  
  39. on (date_sub(cz.date1,interval 1 day)=rm.date2 and cz.cid=rm.cid2 and cz.zid = rm.zid2)
  40.  
  41. left join
  42.  
  43. (select uu.region, ifnull(cc.mft,0) as mft
  44.  
  45. from(
  46.   select zo.region as region
  47.   from car_zone_logs z
  48.   join zones zo
  49.   on z.zone_id = zo.id
  50.  
  51.   where
  52.   date_sub(z.log_date, interval 1 day) <= @startdate
  53.   and date_sub(z.log_date, interval 1 day) >= @startdate2
  54.   and z.zone_id not in (2,3,101,383,378,697,712,818,786)
  55.  
  56.   group by zo.region
  57.   order by zo.region desc
  58.  
  59. ) uu
  60. left join
  61. (select zid2, region, count(distinct if(before_use=0, mid, null)) as mft
  62.   from
  63.   (select
  64.       z.id as zid2, z.region as region, r.member_id as mid,
  65.       (select count(id)
  66.       from reservations
  67.       where member_id=r.member_id
  68.       and state='completed'
  69.       and return_at+interval 8 hour < @startdate2
  70.       and start_zone_id not in (2,3,101,383,378,697,712,818,786)) as before_use
  71.     from reservations r, members m, zones z
  72.     where
  73.       r.state='completed'
  74.       and r.member_id=m.id
  75.       and r.start_zone_id = z.id
  76.       and m.imaginary in ('normal', 'sofam')
  77.       and r.return_at+interval 8 hour <= @startdate
  78.       and r.return_at+interval 8 hour >= @startdate2
  79.       and r.start_zone_id not in (2,3,101,383,378,697,712,818,786)
  80.      
  81.   group by r.member_id
  82.   ) temp
  83.  
  84.   where
  85.     before_use=0
  86.  
  87. group by region) cc
  88. on (uu.region = cc.region)
  89.  
  90. group by uu.region
  91. ) A
  92.  
  93. on A.region = cz.region
  94.  
  95. left join
  96.  
  97. (select
  98. z.region as region,
  99. count(distinct m.id) as mau
  100. from reservations r, members m , zones z
  101. where r.member_id=m.id
  102. and r.start_zone_id = z.id
  103. and r.way in ('round','d2d','oneway')
  104. and r.state = 'completed'
  105. and m.imaginary in ('normal', 'sofam')
  106. and r.return_at+interval 8 hour <= @startdate
  107. and r.return_at+interval 8 hour >= @startdate2
  108. and r.start_zone_id not in (2,3,101,383,378,697,712,818)
  109. group by z.region) B
  110.  
  111. on B.region = A.region
  112.  
  113.  
  114. group by cz.region;
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